Version control of data

msollicito · November 7, 2012 10:17AM

I am trying to decide the best way to version control the DATA in our databases. However, the primary keys / foreign keys will be different in our Staging Database to our Production Database, but I am assuming that is pretty common?

Can SQL DATA Compare handle that? I want to be able to select which data in the Staging database should be copied to Production, but in Production the keys will be different to those in Staging (auto generated identity type keys) because we have some test ids in Staging, etc.

That should work fine in SQL DATA Compare right?

James B · November 7, 2012 1:24PM

Hi,

SQL Data Compare requires a comparison key to know which records should relate to each other. By default, the tool will use the PK for this.

If you have databases where the PK's are different for what is essentially the same data (for instance a record for customer "Joe Bloggs", with ID 100 in Staging but 552 in Production) then SQL Data Compare will see these as unrelated records, and will either add (or update if it's already there) record 100 in the target production DB.

The only way you could map changes from record 100 in the source to, say, 552 (and these are actually the "same" data) would be to specify some other comparison key. See http://www.red-gate.com/supportcenter/Content/SQL_Data_Compare/help/10.0/9067 and http://www.red-gate.com/supportcenter/Content/SQL_Data_Compare/help/10.0/SDC_SelectingObjects#o7591

msollicito · November 7, 2012 7:31PM

So I am guessing that if some other column or combination of columns is also unique, we could use that.. right?
I am pretty sure it must be but I will check with my client!

James B · November 8, 2012 7:32AM

Yeah- if you have some other column which would match up the same record in different DBs, you can use that (so you may have an Identity column for the PK which differs, but also a "CustomerNumber" column which would be consistent; so you can set that instead.

Version control of data

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk