Unify data

Question

Post reply

Unify data

marcueusebiu

Grasshopper

Points: 10
More actions
June 24, 2008 at 6:28 am

#122930

Hello...
I have 2 databases(one original and one a backup) with the same structure of each table(I mean PKs,FKs, column names and so on) on a SQL Server 2005 Pro.
The only thing that is diffrent is the data inside the tables. Some of the tables have FKs.
How can I copy(append) all data from every table inside DB1 into the same table inside DB2 and the data from the foreign tables?
To give an example... I have a table,Customers, that has a PK a column named ID. I also have another table, Requests, that has a column CustomerID. It is possible that I have two diffrent customers with the same ID(one in DB1 and the other in DB2). When I copy thee data from table DB1.Requests in Db2.Requests, I have to be carefull to update the CustomerID with the one copied - otherwise I'd get a messed up database.
Is there a tool that solves this problem?
Thanks
P.S. I've tried the Flyspeed DB Migrate to SQL Server but this tool does not solve my problem.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 1

i had to do this on a small database last week

run an insert for each table where the PK is not in the dest db

insert new_db_tablea

select * from old_db_tablea where PK not in (select PK from new_db_tablea)

if you have identity columns then either use SSIS or you will have to list every column in the table

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

Have you tried Redgate's SQL Data Compare?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

marcueusebiu Grasshopper Points: 10 More actions · Answer 3

GilaMonster (6/24/2008)
Have you tried Redgate's SQL Data Compare?

I've tried SQL Data Compare... very nice tool... but when I syncronize the databases, it overwrites the Customer with the same ID from DB1... I want to keep all customers - from DB1 and DB2.