SQL Server Performance Forum – Threads Archive
Combining databases with identity cols
What is the proper procedure for combining tables where Identity Columns are used for Primary/Foreign Key links? I have two databases, with identical structures, different data. Tables in the db’s are linked by identity cols, so each of the two tables I have to combine might have an ID col value of 1 or 2 or 3, etc. I can’t import the second table into the first, because they both have an ID cols that equal 1 that link to another table.In TABLE 1:
HEADER , HEADER_ID=1 –> Detail HEADER_ID=1, DETAIL_ID=ABC
HEADER , HEADER_ID=1 –> Detail HEADER_ID=1, DETAIL_ID=DEF
HEADER , HEADER_ID=1 –> Detail HEADER_ID=1, DETAIL_ID=GHI
In TABLE 2:
HEADER , HEADER_ID=1 –> Detail HEADER_ID=1, DETAIL_ID=TUV
HEADER , HEADER_ID=1 –> Detail HEADER_ID=1, DETAIL_ID=WXY
HEADER , HEADER_ID=1 –> Detail HEADER_ID=1, DETAIL_ID=ZZZ To import, I will need to transform the linkage IDs of TABLE 2 so they do not conflict with the ID’s already assigned in TABLE 1. Table 2 links to yet other tables that also link DETAIL_ID–>DETAIL_ID, so this problem comounds all the way down through. I feel pretty sure that this must be a common task, so I suspect there must be some way to automate this task. Appreciate any help on this.
you can define your identities to start from diffrent seeds,
for example, at database 1 it will be 1 while at db 2 it will be 100000 (any mumber which will not come at db1) then you can import them without problem else, you can define diffrent increment. say db 1 it will be 1 and at db 2 it will be 2
so db1, you will have id like 1,3, 5
while db2 you have 2,4,6
—————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/
Different seeds will make you run into problems. Perhaps if you need not more than 9, you can use an increment of 10. Or 99 with an increment of 100. But this will cause you headaches. You can copy the ‘original’ identity value into a separate column, then just lookup the new identity for the original identity value to replace a foreign key reference in other tables that you’re importing.
]]>