May 22, 2010 at 6:41 pm
So I've got multiple databases that have identical schemas but mostly unique data that I want to combine into one database. The problem, that I haven't ever dealt with before, is that most tables have an identity column. So when I 'insert into' the combined table I figure that I have to turn off the identity property on the column so I can do the insert. The other issue is that the identity fields in each table all started with 1 and incremented from there...so after the first tables data is moved there becomes a PK violation. What should I do about this? Create a script that will update each of the identities to something that would be unique so I can insert them into the table? Or is there an easier way? I have about 200 tables in each database to combine.
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
May 25, 2010 at 7:21 am
This was removed by the editor as SPAM
May 25, 2010 at 9:50 am
No. However I figured out another issue today. The identity columns are being used as the Primary Key on these tables and there are FK relationships setup with other tables. So, if I don't move over the PK's from the original tables then I won't be able to reference the data in the FK tables. So...I suppose I would need to update the PK's to a unique range for each DB, then copy the PK's over with IDENTITY INSERT?
What would then be the best way to accomplish this? Would it be to set the seed to a big enough range between and then somehow have the table rebuild the PK through some statement? And then how would the FK's be rebuilt as part of that process since they are a contraint?
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
May 25, 2010 at 10:41 am
There's no good way to do this. I've done what you suggested, but it's the same as what Stewart wrote. You need to create a table that tracks the old and new identity values from the PK parents and use that for the children.
Basically if you have a parent of ID1, and you rebuild the table or insert this row into the new db and it has id 24054, you need to put those values in a table. Same for all parents. Then as you move over child rows, you update their FK values from 1 to 24054.
May 25, 2010 at 11:07 am
Steve Jones - Editor (5/25/2010)
There's no good way to do this. I've done what you suggested, but it's the same as what Stewart wrote. You need to create a table that tracks the old and new identity values from the PK parents and use that for the children.Basically if you have a parent of ID1, and you rebuild the table or insert this row into the new db and it has id 24054, you need to put those values in a table. Same for all parents. Then as you move over child rows, you update their FK values from 1 to 24054.
Wouldn't that create a nightmare for writing new sql in the future? Wouldn't every query need to join this new table every time to keep the integrity?
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
May 25, 2010 at 11:38 am
No, this new table is only for the ETL movement. Once the data is moved, it isn't needed, but it is needed in order to ensure all the data is correctly moved and the FKs line up correctly.
May 25, 2010 at 11:49 am
Steve Jones - Editor (5/25/2010)
No, this new table is only for the ETL movement. Once the data is moved, it isn't needed, but it is needed in order to ensure all the data is correctly moved and the FKs line up correctly.
AHHHHH! Now it makes perfect sense. Now to sit down and write it for 200+ tables that aren't necessarily linked by the same keys. :crazy:
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply