Version control of data

[msollicito]

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?
_________________
Contact me on:
Email /Yahoo! Messenger michelle.sollicito@yahoo.com
GoogleTalk/MSN michelle.sollicito@gmail.com

[james.billings]

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]

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!
_________________
Contact me on:
Email /Yahoo! Messenger michelle.sollicito@yahoo.com
GoogleTalk/MSN michelle.sollicito@gmail.com

[james.billings]

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.