SQL Server 2005 - best way to move data between two databases when primary keys have changed

Question

Hi, i know this should be db 101, but its just not as clear as it can be for me. I am using SQL2005 express and i want to copy data from databaseA to databaseB. DatabaseB already contains existing data - it may even contain data with pk's that have changed or dont exist - for example:

DataBase A pk1 = peaches pk2 = apples

DataBase B pk1 = peaches pk2 = oranges pk3 = apples

now both A & B have related tables that are tied to the pk.

Historically i would write an app that selects the data from A and copies it to B via its Insert/Update procs (using .NET), but obv this is very cumbersome, tho some advantages are i could have a dropdown that allows you to select A to copy - and B to copy to if it exists, or say add new - for instance:

(dropdown - select source) Peaches Apples

(dropdown - select target) -new- peaches oranges apples

I was going to use SSIS, but the target db has only SQL express so this is not available nor do i have time to learn it (ive used dts much in the past, but not this newer tool). In SQL2000 i would just use DTS, but with the more complicated schema now im not even sure i would trust that.

Any suggestions or should i just continue to write my custom apps to migrate data from A to B?

Im looking forward to everyones suggestions - i would love to continue down a path that i feel is the best way to do this :-)

Answer 1

if the servers are on the same network add a linked server (look up in transact sql books online). Then you can run queries across the two servers.

SQL Server 2005 Express sucks in that it does not include SSIS. However you can also use BCP (bulk copy, look this up in the transact sql books online as well) to copy your data to different tables on the target server. It is probably in your path so from dos you can type bcp /? to get a list of options. Once the data is in a table on your target server you can run queries against it.

You do need to create a mapping between keys. I don't know how from peaches/apples you get peaches/oranges/apples. No doubt there is some business logic. Once you link the servers or get the data on one server. You can either use business logic to map old primary keys to new keys. Or a mapping table (oldkey, newkey) or (oldkey1, oldkey2, newkey1, newkey2, newkey3) that you manually fill out.

Answer 2

Not real familiar with the limitations of SQL Server Express, but could you backup? And then import as another database?

Answer 3

You might look into merge replication - http://msdn.microsoft.com/en-us/library/ms165713(SQL.90).aspx

Is it always a one way push? A->B?

As a note, if you could get SSIS, you should take a little time to learn it. It's better than DTS imho.

Answer 4

sql 2008 has an interesting feature called Change Data Capture that might be interesting down the road for you http://blog.benhall.me.uk/2007/06/sql-server-2008-change-data-capture-cdc.html