Version control of data

Compares and synchronizes SQL database content.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, Michelle Taylor, chengvoon.tong

Version control of data

Postby msollicito » Wed Nov 07, 2012 4:17 pm

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
msollicito
 
Posts: 5
Joined: Wed Nov 07, 2012 4:14 pm
Location: Marietta, GA 30067

Postby james.billings » Wed Nov 07, 2012 7:24 pm

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
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Thanks..

Postby msollicito » Thu Nov 08, 2012 1:31 am

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
msollicito
 
Posts: 5
Joined: Wed Nov 07, 2012 4:14 pm
Location: Marietta, GA 30067

Postby james.billings » Thu Nov 08, 2012 1:32 pm

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.
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.


Return to SQL Data Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests