Skip to content
← SQL Source Control

How can we improve SQL Source Control?

SQL Source Control: Static Data

Categories

JUMP TO ANOTHER FORUM

(thinking…)

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=50087

636 votes
Vote

We're glad you're here

Please sign in to leave feedback
Signed in as (Sign out)
Close
Close
You have left! (?) (thinking…)
JackAce shared this idea  ·   ·  Report…  ·  Admin →
How important is this to you?

We're glad you're here

Please sign in to leave feedback
Signed in as (Sign out)
Close
Close
under review  ·  Kendra responded  · 

Hi 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 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

If there are other use cases where a migrations approach would add value to the team – such as easily being able to run data modification statements as part of deployments, or being able to customize the TSQL for schema changes – then changing from SQL Source Control to SQL Change Automation might make sense.

We're glad you're here

Please sign in to leave feedback
Signed in as (Sign out)
Close
Close
Submitting...
An error occurred while saving the comment
  • trhff commented  ·   ·  Report

    2020 now.
    Really disappointing.
    We are searching for alternatives right now, as Redgate data compare to a folder will not be supported anymore (currently its buggy and will not be available in future).
    That's it with redgate and deployment automation, bye, bye
    You had many years

  • Asaf commented  ·   ·  Report

    2019 now.
    Really disappointing.

  • James Penman commented  ·   ·  Report

    Absolutely necessary for the reason given. We have lots of tables containing default/global records and user specific content.

  • Anonymous commented  ·   ·  Report

    This is definitely something we need/want in our environment using shared databases. Today we selectively commit rows from certain look up tables that would not be ready to migrate for QA. We use the SQL DataCompare today to do this but a simple way to do the same in SC Tool would be awesome. thanks guys!

  • Morgan commented  ·   ·  Report

    Just checking since its almost 2018 now. Any update?

  • Anonymous commented  ·   ·  Report

    This is a great idea. The filter is basically a WHERE clause. What would also be nice would be to allow excluding some fields - for example, we have a global ConfigurationSettings table for our turn-key product, and every client will have every setting, but the values for each setting would be different and we wouldn't necessarily want to store those values in source control.

  • Jon commented  ·   ·  Report

    we have a table of seed numbers that gets updated each time a new record is created. After creating the initial static table we should be able to ignore specific changing values in fields that are volitile (yes it is a poor design)

  • Tim Ververs commented  ·   ·  Report

    Any idea when this is going live? Since the ADMIN of redgate submitted this idea in 2010..

  • Jon Baggaley commented  ·   ·  Report

    This is particularly annoying for us as we have a table containing identity values of policies which are set (design before my time) and every time we create a new policy whilst working on our dev version, the numbers naturally increment. We have no interest in saving the changes of this static table so would like to exclude changes after the initial version.

  • Kevin W commented  ·   ·  Report

    We have different project teams adding/updating shared configuration tables. When one project is done in DEV, they would like to commit just their static row changes to source control for future deployment ignoring what other teams are doing. Please help!

  • Kent G commented  ·   ·  Report

    add a way to increment identity fields by say 10000 of all tables in the project and add data starting at this new value.

  • Timothy Batts commented  ·   ·  Report

    I was thinking extending the UI for data changes to function more like Data Compare would be nice, where you have inserts, updates, and deletes, and you can check only the rows you wish to change in source control.

    This would also be helpful in shared development scenarios, where multiple developers may have changes to static rows in the same table for different work items. This way they can check in only their own changes individually and link them to the respective work items they actually belong to.

  • Wade Tatman commented  ·   ·  Report

    The product really needs this one! My commits are constantly littered with data updates due to timestamps changing (automatically via trigger). I don't even use SQL Data Compare because of this, as it would wreck my audit data in production. Source control would benefit by being faster too.

  • Anonymous commented  ·   ·  Report

    Add a filter for static data, similar to the feature available in SQL Data Compare. This will facilitate source controlling data for tables that contain a mix of static system data and user content.

  • Michael Melancon commented  ·   ·  Report

    Really need to be able to select data to commit like the merge in Data Compare. I'm currently having to keep 2 copies of each database in my instance so I can make sure only the data I want to commit gets in

  • sheldonhull commented  ·   ·  Report

    I've had management ask for this before. This would make it much more flexible for our usage.

  • Klaus Espernberger commented  ·   ·  Report

    We also would like to use Source Control for data content.
    But Link Static Data for the whole table is way too much of data.
    We would need this with a where clause to be selective.
    We do have many tables that contain both static and user data.
    So please put this onto the development Goals.

  • James commented  ·   ·  Report

    I figure that SQL Data's engine is being used for the static data comparison, so why not give the ability to customize which columns are compared? (this is a pretty old request so maybe it's there..that would be awesome)

  • Jorge Salazar commented  ·   ·  Report

    Almost 2 and a half years of the initial request and still no solution on this item?
    We use SQL Source Control 3.5.4.332 and there is no solution on this...
    Is really that hard to implement?

← Previous 1