Applies to
Summary
This article explains how to work with static data in ApexSQL Source Control.
Description
The main focus of the ApexSQL Source Control tool is to track changes in object structure (schema level). But, developers can also link table data to source control to track and compare changes, as well.
Static data, also known as reference or lookup data, is data that is necessary for the first deployment so that the application which is built upon that database can work properly. This data can be any set of predefined values that are rarely changed, e.g. postal codes, lists of states e.g. NY, etc.
There are two ways to link data:
-
From the Static data window, which can be accessed from the database right-click context menu in the Object Explorer panel:
In this window, only tables with the Primary key will be listed. After the Link/Unlink button is clicked, data from all checked tables will be linked, while data from all unchecked tables will stay out of source control
Quick tip:
When table data is linked, it doesn’t mean that it is committed to the repository, just that developers can start working with it through ApexSQL Source Control (commit, changes tracking…)
-
From the Object Explorer panel, by clicking on the Link static data menu item for the selected table:
Using this command on the tables without the Primary key will raise the following message:
To unlink data from source control at a later stage, uncheck that table in the Static data window and click the Link/Unlink button or click the Unlink static data right-click context menu item on that table in the Object Explorer panel:
Quick tip:
Unlinking data doesn’t mean that this data will be deleted from the source control repository. That way developers who are still working with that data can continue to work with it normally
No matter which way is chosen to link table data after its being linked, the object status icon color will be changed to ‘’yellow’’:
The linked table data will be represented in the Action center tab with the name of its table with the addition of the “[Data]” tag:
Once it is committed to source control, other users will see it in the Action center tab, but to track changes on it, they must also link it from a local machine. It can be done by ways explained above or directly from the Action center tab, by clicking the message: “Click to link static data”:
In the Action center tab, developers can see any changes and apply them to or from source control in the same way as changes on the schema level.
In order to ensure the integrity of an object and data structure between a database and source control and to prevent errors when working with the data tables (objects), ApexSQL Source Control will not allow applying any data changes to or from source control, if any schema difference is detected between the selected table in a database and source control :
This issue can be resolved by applying both schema and data changes at once, or applying the first schema and then data changes.
Working with a large amount of data
The option that can reduce performance impact is the Show different rows only option and can be accessed from ApexSQL Source Control options, under the Action center tab:
If the option is checked, only rows where differences are detected will be shown in the Differences section of the Action center tab:
Updating static data scripts
- Note: All users that linked and committed table data to the source control repository with any of the previous ApexSQL Source Control versions, will have to update the data scripts after an upgrade to the ApexSQL Source Control 2021 version is done
After the upgrade to the ApexSQL Source Control 2021 version is done and the Action center tab is opened for any database that has already linked table data, all data will be shown as ‘’only present on the repository (unlinked from database)’’:
The status object icon, for the tables with the linked data in any of the previous ApexSQL Source Control version, will be a blue dot showing that the data is not linked:
Link table data, one by one, through the Action center tab by clicking on the ‘’Click to link static data’’ link for every listed table:
Or right-click on the linked database in the Object Explorer panel and choose the More source control options – Static data command. In the Static data window check desired tables and click the Link/Unlink button:
After the Action center tab is refreshed, the linked table data will be shown as a difference, with scripts present on the database and repository side:
The shown differences are not the data differences, they are the differences in the way the data script is written. Check all data changes and click the Apply button to commit all those listed data changes to the source control repository. After this is done, the Action center tab will be refreshed and the database and the repository will be synced.
From this point on, the update of the data scripts is done and the work with the data can continue.
Resolve data conflict
- Note: This option is available from the ApexSQL Source Control 2021 version
When the data conflict is detected the following screen will be shown in the Action center tab:
Here will be present three resolve conflict solutions – Keep local, Take from repository, and Merge:
When the Keep local solution is chosen and the Confirm button is clicked, in the Action column the action will be changed to ’’Commit to source control’’ (arrow to the right) and the Apply button icon will follow that change when the change is checked:
Choosing the Take from repository solution will change the action to ’’Commit to database’’ (arrow to the left) and change the Apply button icon accordingly when the change is checked:
-
Note: For now the merge data solution is not supported, if the Merge solution is chosen the following message will be raised:
Based on the chosen resolve conflict solution the change, after the Apply button is clicked, will be committed to the source control repository (Keep local), or committed to the database (Take from repository).
FAQs
Q: How can I delete data from the source control repository?
A: Simply unlink the data table locally and commit that change on source control repository from the Action center:
Additionally, anyone who has access to the source control repository with proper rights can delete the data files.
Q: Can I use this feature to track changes to all data in the database?
A: Yes, you can. But it is not recommended to be used with a large amount of data because it can be performance intensive
Q: Can I include data in builds e.g. with ApexSQL Build along with schema changes?
A: ApexSQL Build supports (can use) data committed using ApexSQL Source Control.
