How to commit, deploy and take latest change for master table values with redgate and teamcity.?

• 
  Do you mean a table in the SQL Server master database? If so I don't have a good suggestion for you within Redgate SQL Source Control. You will need to manage this some other way.
  
  Do you mean you want to insert data into a reference table or lookup table in your source controlled database? If so, assuming the table has less than about 1000 rows use the 'static data' feature:
  https://documentation.red-gate.com/soc6/common-tasks/link-static-data
  
  If it is a bigger table either use a 'migration script':
  https://documentation.red-gate.com/soc6/common-tasks/working-with-migration-scripts
  
  Or, if this is a regular task, consider using ReadyRoll or some other process instead. SQL Source Control 'migration scripts' work well for very occasional changes on small-ish databases but they don't scale very well to bigger databases and frequent use.
  

  AlexYates February 27, 2018 10:04

  - 0 +
• 
  Thank You for the reply. I Insert records to a lookup/reference table in the source controlled Database. We have source controlled only schema not the data. In order to commit a row in a lookup table which method I should follow?
  At a time mostly one entry is made to a table(Insert records also depend on data in other tables like the created by column from the users table). We have been using Migration Script feature for this. But while deployment using team city in the Build step we face many issues because there are no data in dependent tables.
  If we use Static Data concept the values in some columns are not same in Dev and Production environment.
  Please help me to resolve this.
  Thanks in Advance.

  deeps May 04, 2018 11:31

  - 0 +
• 
  How much data in your lookup tables?
  
  If roughly 1,000 rows or less try the Static Data option. Obviously you will need to ensure all dependencies are also in source control.
  
  If an order of magnitude more than that it's harder. You could try migration scripts but they are complicated and it's easy to get yourself into a pickle.

  AlexYates May 09, 2018 13:31

  - 0 +
• 
  If you need different static data in dev and prod you could try something like this:
  https://www.red-gate.com/blog/database-development/customer-specific-database-deployments
  
  Other options:
  Use a post deploy script that calls a scproc.
  Sproc should be environment aware.
  Sproc should do something like:
  
  IF (DEV) {Make static data looklike this}
  IF (PROD) {Make static data look like that}
  
  This way it's all in source control - but if your static data tables are big that sproc will be massive.

  AlexYates May 09, 2018 13:35

  - 0 +
• 
  Or, if the static data is massive, don't use source control to solve the problem. Do it some other way. Source control doesn't like massive files.
  
  (Although if poss source control is best.)

  AlexYates May 09, 2018 13:36

  - 0 +

Add comment

Please sign in to leave a comment.