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
  • Timothy Bussmann commented  ·   ·  Report

    We have a scenario where we have table maintenance screens in a user interface to allow the user to add/modify/delete lookup type values. As part of our application we provide a number of lookup values that are system defined and cannot be modified. We would like to be able to source control the system defined values, but also allow for user defined values to be unaffected. It does not make sense for us to use a separate table for the user defined lookup values because that would complicate the data schema and application design. It would help us a lot if we were able to source control static data that is filtered by a where clause (e.g. IsSystemDefined = 1 in our case).

  • Nate Suver commented  ·   ·  Report

    This feature would be absolutely huge for us. We have a number of configuration tables that have a mix of default data and user defined data, having the ability to use ssc to manage this data right in the source repository would simplify our build configuration tremendously.

  • Kasper B commented  ·   ·  Report

    Respecting a WHERE clause would eliminate the use of a view, so that is what I would prefer.

  • BarryK commented  ·   ·  Report

    We just started using SQL Source Control 3 and it is a great feature. However the Link Static Data isn't much use without a where clause. We have numerous tables that contain both static and user data. Thanks for considering this feature.

  • Gabriele Ferreri commented  ·   ·  Report

    We are actually using SQL Source Control 3 and it's important for us to be able to commit Static data using a where cause like in SQL Data Compare.
    Please add this feature.

  • Phil Helmer commented  ·   ·  Report

    Is there any update on this? We are having to add a lot of steps to our process because this feature is missing. Granted, SDC can do the work, but now we are forking our work by tool (1 method for schema, 1 for data) instead of usage (1 method for the "always like this" items and another for the "where are we deploying it?" items).

  • Paul Jenkins commented  ·   ·  Report

    I don't know of an orgranization that does not require audit columns on all of their lookup tables (static data). Not having the ability to do this makes the static data mechanism useless for organizations with auditing standards.

  • Corrin Lakeland commented  ·   ·  Report

    Hi, just wondering if anything happened with this now static data is live?

  • Jason Duffett commented  ·   ·  Report

    The problem we have is that the current EAP builds of SQL Source Control aren't usable because of the limited data support.

    We use the tools in the following way:
    1. A single SVN repository
    2. SQL Source Control for developers to check-in changes to this source tree.
    3. Custom SQL Compare and SQL Data Compare scripts to deploy changes from the repository to our test, staging, production environments

    Testing we've done with the pre-release SQL Source Control have failed because it always tries to overwrite the data in the repository.
    We won't be able to use the new release unless either we can completely disable data support in SQL Source Control, or the data support is enhanced to allow us to specify which columns to source-control.

2 Next →