How to commit SQL Server table static data to a source control repository


Once a SQL database is committed to a source control repository with all of its objects, the next task is to commit the static data. Static data is non-transactional data from tables that generally don’t change often like postal codes, department names, etc.. Many teams treat this type of data akin to the database structure itself, by versioning it to track changes and deploying it from source control just like database objects. As data is versioned in source control, managing it can be automated as well.


A 3rd party tool ApexSQL Script will be used in this article to commit static data from a SQL database to the source control repository. In this article, manual commit via the GUI will be covered, as well as automation via the CLI.

ApexSQL Script is a tool that can script all SQL Server objects and data into a single file or to script each object and data as an individual file. It can package scripts into a C# solution or into an executable installer which can be run without installing any additional software. Additionally, it can script and commit SQL Server objects and data to a source control repository using native support for Git, Mercurial, Perforce, Subversion, and Team Foundation Server source control systems. All of these processes can be automated and run unattended using the CLI.

In the following steps, the whole process of committing SQL Server table static data with ApexSQL Script will be explained:

  1. Prerequisites for committing only static data is that there is a created source control repository on and that tables are already committed to that source control repository. In this example, BitBucket is used as hosting service and Git is used as a source control system
  2. Run ApexSQL Script

  3. Connect to a desired SQL Server, select a database and click the Load button in the New project window:

    SQL database selection in the New project window

  4. Once a desired database is loaded and its SQL Server objects are shown, click the Data button from the Home tab, under the View section:

    Data view in the main application window

  5. The Data view will be shown, along with the tables and their columns:

    Data grid with tables and columns in the main application window

    In case that not all tables/columns/rows are needed, all of them can be filtered to include/exclude specific ones. Learn more about narrowing the data for object scripting from here

  6. Once the desired data is checked, click the Save button to save project:

    Saving current project settings to a project file

  7. Once the project file is saved, click the Script button from the Home tab to initiate the Script wizard:

    Choose the Scripting mode in the Script wizard

    Choose the Output type in the Script wizard

  8. Choose Data for the Scripting mode and Source control as the Output type, as in the screenshots above
  9. Under the Dependencies step, check whether to include dependent objects or not:

    Dependencies step in the Script wizard

  10. In the Source control options step click the Setup button:

    Source control options settings in the Script wizard

  11. The Source control wizard will be shown and in the first step, choose the source control system. In this example, Git source control system will be selected and the Next button is clicked:

    Choose the Source control system in the Connection type tab of the Source control wizard

  12. In the following step, source control login information is entered and the Finish button is clicked:

    Source control login settings in the System login tab of the Source control wizard

    Learn more about setting up the Source control wizard from article on this link

  13. Once the Source control wizard is closed, the source control information will be loaded:

    Source control options information in the Script wizard

  14. If there is a need to create a batch or PowerShell script, it can be achieved with a click on the Automation script button and the window with the same name will be opened. Once the Automation script window is opened, if the Project file option is checked, all CLI switches will be replaced with the project file switch:

    Automation script window for creating batch or PowerShell scripts

  15. Click the Save button, in the bottom-right corner of the same window, so that project file and batch file are saved on the specified location

  16. After closing the Automation script window, click the Commit button to initiate the committing to source control repository process

  17. Once the committing process is finished the Results dialog will be shown:

    Results dialog with the information about the execution results

  18. The commit results can be also viewed on the source control repository. As Bitbucket was used as a Git hosting service in this article, connect to previously created repository and click the Commits tab on the left side panel:

    Tabs at Bitbucket hosting service website

  19. In the Commits tab, click to view the latest commit:

    Commits tab at Bitbucket hosting service website

  20. The new window will show all files that were changed during the commit process:

    Changed files in the commit process

  21. To see SQL script that contains Insert into statement for adding static data to specific table on the source control repository, click on the desired .sql file and it will lead to the selected file which can be viewed with a click on the View file button:

    Static data .sql files

  22. The new window for the selected file will be shown and it can be downloaded to view it with a click on the Open raw button:

    Static data .sql file script

  23. Once downloaded, it can be viewed in SQL Server Management Studio query window or any text editor:

    Static data .sql file script opened in SQL Server Management Studio

Automating the static data commit process with ApexSQL Script CLI

The above described process can be automated and run unattended, by scheduling the created batch or PowerShell script with SQL Server Job or using the created project file for scheduling a PowerShell project.

In the following example, creating and scheduling of a PowerShell project using the created project file from ApexSQL Script will be shown:

Schtasks.exe /Create # creates a PowerShell task
/SC DAILY # task frequency
/ST 00:00 # start time for a task
/ET 05:00 # end time for a task
/TN CommitData # task name
/F # a value that overwrites the task, if already exists
/TR # path for a task that will be run at the scheduled time
“ApexSQLScript” # application’s path
/project:”MyProject.axsc” # the path of the saved project file
/v # prints all messages in the console
/f # overwrites existing script files
/out:”Output.txt” # redirects console output to a specified file
@echo ApexSQL Script return code is %errorlevel% >> /out:”Output.txt” # return code upon finished commit process

After running the following PowerShell project, the results will be shown as:

Loading project MyProject.axsc
Set scripting mode: data
Set output type: Source control
Script each object to an individual file

Creating script for Table: Customer
Creating script for Table: BusinessEntityAddress
Creating script for Table: ProductReview
Creating script for Table: ProductSubcategory
Creating script for Table: SalesPersonQuotaHistory
Creating script for Table: AddressType
Creating script for Table: ProductInventory

Script processing to source control
File(s) checked in to Git
ApexSQL Script return code is 0

If an error is encountered during the commit process, ApexSQL Script will provide an appropriate return error code.

Check out all ApexSQL Script CLI switches in the article on this link, with working examples.

If for any reason, the process needs to be attended and database/source control login credentials need to be entered each time, check out the article on this link on how to allow prompting for login credentials.


October 7, 2016