May 19, 2014 at 11:07 pm
Hi,
How to merge the data from one database to another if we have identity column on both the database.
If we are merging two companies,we need employee table of 2 database and insert them into first database and corresponding fkey tables say some 7 tables.how to merge if the table is having identity column.
DatabaseA has 7 Tables
Namely T1,T2,....T7
DatabaseB has 7 Tables
Namely T1,T2,....T7
T1 is master
T2 is Child
DatabaseA
T1
|
----------------
| |
T2 T3
| |
----------------- ---------------
|| | |
T4T5 T6 T7
DatabaseB
T1
|
----------------
| |
T2 T3
| |
----------------- ---------------
|| | |
T4T5 T6 T7
All the tables have interrelationship as shown pkey and Fkey
All the T1...T7 have Pkey with identity column starting from 1 and corresponding Fkey column in their child tables
Database A Table information Rows
T1-10000
T2-5000
T3-5000
T4-5000
T5-5000
T6-5000
T7-5000
Database B Table information Rows
T1-20000
T2-10000
T3-10000
T4-10000
T5-10000
T6-10000
T7-10000
Now i want to merge all the data from Database B to DatabaseA
how can i merge since in DatabaseA id for T1 starts from 1,2,3,4...and so on...
DatabaseB id for T1 also starts from 1,2,3,4...and so on...
and the fkey tables also have same 1,2,3,4...and so on... with reference of parent table
Thanks!
May 20, 2014 at 4:01 am
Not enough details to suggest a solution.
What do you mean by merge in your case?
1. May you have the same data in both databases?
If Q#1 = Yes:
2. Will this "same" data have the same PK values?
If Q=2 = No:
What will link the "same" data in two databases? Any legacy keys?
If Q#1 = No:
Do you only need insert data from DatabaseB to DatabaseA?
Just generic answer:
There are two common solution to "database merging":
1. Design your PrimaryKey to be compound and include "DatabaseSource" which will tell the database your data coming from
2. Design your PK based on identity to use non-overlaying ranges.
May 20, 2014 at 4:21 am
What do you mean by merge in your case?
moving Database B details to Database A
1. May you have the same data in both databases?
If Q#1 = Yes:
Yes
2. Will this "same" data have the same PK values?
If Q=2 = No:
YES it will have same values.
It is like merging 2 companies companyA acquired CompanyB so all the emp from DatabaseB will move to DatabaseA
where DatabaseA empid starts from 1 and ended with 10000,
where DatabaseB empid starts from 1 and ended with 20000
now need to start as 100001 for the data from databaseB and end with 30000 and corresponding child tables should also reflect the newid.
What will link the "same" data in two databases? Any legacy keys?
If Q#1 = No:
the database table is same in both the database.
[Quote] Do you only need insert data from DatabaseB to DatabaseA? [/Quote]
YES
May 20, 2014 at 4:22 am
You'll have to let the identity values be regenerated by the system that you're moving everything into. You can capture the new values using the OUTPUT clause. With that you can associate the foreign key values to the newly generated primary key values. It will help immensely if you have an alternate key on one (or more) of the columns that lets you identify a row by more than just the ID. Otherwise, you can still make it work, but you'll have to rely on data order which means you may want to sort the information yourself so that it's always in the same order.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply