4 results found
-
SQL Source Control Static Views (or Table Subsets using WHERE clause)
I would like the ability to source control a subset of a table (perhaps using a view).
I have inherited a database where lookup data is intermixed with user defined data. We reference the lookup data in the table using a code (VARCHAR) that is unique when another field is NULL, so it is possible to ensure that records are created/updated/deleted without concern for the primary key involved.
For more information, you can refer to this forum post:
http://www.red-gate.com/messageboard/viewtopic.php?p=50087636 votesHi everyone. I have merged some User Voice items on this topic of “filtered” static data, as there was significant overlap. I want to share our current guidance on handling scenarios where you need to version a subset of the columns and/or rows in the table.
With SQL Source control, the best option at this point is to use a post-deployment script for this purpose.
SQL Source Control introduced pre- and post- scripts in v6.3.
A post-deployment script gives you a good amount of flexibility over exactly which rows or columns of data you want to include in your project. Example post-deployment scripts for static data are here: https://documentation.red-gate.com/soc7/common-tasks/working-with-pre-post-deployment-scripts/static-data
If you make heavy use of Static Data, we have stronger support for this in SQL Change Automation.
SQL Change Automation:
- Supports column filtered static data tables in the SCA plugin in SSMS
- Supports multiple post-deployment scripts, in case there is…
-
Make SQL Data Easier to Merge (Static Data)
Currently, SQL Data files contain a bunch of insert statements, but the order of the insert statements can be inconsistent. For example, new records might get inserted on the top or the bottom. Also, multiple SET IDENTITYINSERT statements sometimes get created. This makes it difficuly to merge data changes between multiple branches in TFS Source Control. If the Insert statements had a consistent order and only included one SET IDENTITYINSERT diffs and merges would be easier.
530 votesThanks for this suggestion and for the many comments and upvotes. I realize that this is a pain point.
I have a few shorter-term workarounds to summarize as well as some information on the longer roadmap in this update. I know these shorter-term workarounds aren’t perfect (I summarize the pros and cons), but I’m posting them as they may help a few folks.
Workaround 1) When data changes to static data need to be made, use a “relink the table” pattern
One can “cleanly rescript” a static data table in SQL Source Control by:- Unlinking the static data table
- Committing
- Relinking the static data table
- Committing
Pro: This works with the GUI and requires no special knowledge or comfort with TSQL. This may help folks with just a few static data tables.
Con: This requires extra steps and results in extra commits in the history, which I realize can… -
Support different static data per customer
I have a single schema for all customers but different reference data for different customers. It would be nice if I could manage this all within SQL Source Control. This should also be understood by SQL Compare and my continuous integration system when it comes time to deploy this to my different environments and customers' sites.
254 votesHi all. Thank you for your votes and feedback on this issue over the years. Here is our current guidance for this suggestion:
Post-deployment scripts give you flexibility for static data
With SQL Source Control, you can now use a post-deployment script to “dynamically” deploy static data based on a factor such as @@SERVERNAME or other query-able conditions.
SQL Source Control introduced pre- and post- scripts in v6.3.
An example post-deployment script which shows how to control deployment of static data by environment is here: https://documentation.red-gate.com/soc7/common-tasks/working-with-pre-post-deployment-scripts/static-data
If you make heavy use of Static Data, we have stronger support for this in SQL Change Automation.
SQL Change Automation:
- Supports column filtered static data tables in the SCA plugin in SSMS
Supports multiple post-deployment scripts, in case there is a preference to manage static data tables in dedicated post-deployment scripts - Allows approaches like bulk loading larger static data tables by supporting SQLCMD…
- Supports column filtered static data tables in the SCA plugin in SSMS
-
static data, Bulk insert for get latest
Do not use transactions but use bulk insert in order to reduce the get latest time. A 'flush and refill' approach using bulk insert should be faster in hours with 10K plus records.
A two processor, dual core computer with 8GB of RAM and a Windows experience index of 6.5 takes over 8 hours to get lates on 50K records.
10 votesThanks for this feedback and for the votes for this suggestion.
For users who need to manage larger volumes of static data, we currently recommend versioning the database with SQL Change Automation. SQL Change Automation has stronger support for static data:
- Supports column filtered static data tables in the SCA plugin in SSMS
- Supports multiple post-deployment scripts, in case there is a preference to manage static data tables in dedicated post-deployment scripts
- Allows approaches like bulk loading larger static data tables by supporting SQLCMD variables in migration and post-deployment scripts
I do understand that you are looking for this feature in SQL Source Control, but wanted to surface this option for other readers who may be interested in either tool.
- Don't see your idea?