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

deeps

Hi I am trying to automate the DB deployment process. As I am a beginner I have many doubts and confusions.
In case to insert or update a row to a master table (not frequently) should I use migration script or some other methods.
Please help me to find a solution for this. Master table has dependency with other tables as well(like a column in Master table has a section column which has dependency with section table).

AlexYates

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.

deeps

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.

AlexYates

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

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

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.)