May 28, 2013 at 7:16 am
Good Morning Everyone
I hope that everyone had a very nice weekend.
I am inserting rows from one database to another. The tables that I am using as the source, has an Identity column that is related and has foreign key constraints to each table that I want to use. I have the parent table in the destination filled with data. What would be the best and most efficient way to insert the other data based on the Identity column of the parent table? I would rather not use the IN clause. I am thinking that a JOIN would be the most efficient.
I am using SELECT * INTO <DestinationDatabase>dbo.<TableName>
FROM <SourceDatabase>.dbo.<TableName>
so that SQL Server will create the tables for me.
I appreciate any and all comments, assistance and suggestions.
Thank you in advance
Andrew SQLDBA
May 29, 2013 at 10:53 am
AndrewSQLDBA (5/28/2013)
Good Morning EveryoneI hope that everyone had a very nice weekend.
I am inserting rows from one database to another. The tables that I am using as the source, has an Identity column that is related and has foreign key constraints to each table that I want to use. I have the parent table in the destination filled with data. What would be the best and most efficient way to insert the other data based on the Identity column of the parent table? I would rather not use the IN clause. I am thinking that a JOIN would be the most efficient.
I am using SELECT * INTO <DestinationDatabase>dbo.<TableName>
FROM <SourceDatabase>.dbo.<TableName>
so that SQL Server will create the tables for me.
I appreciate any and all comments, assistance and suggestions.
Thank you in advance
Andrew SQLDBA
So the "parent" table in the destination database is defined with an IDENTITY column? Given that you've already created the FK relations between tables in the source database using the IDENTITY values assigned in the "parent" table there, do you really want the same rows to have different IDENTITY values in the source "parent" table and the destination "parent" table so that you have to reassign the FK values in all the destination "child" tables when you move the data? That seems mightily inefficient.
Does the destination database need to have an IDENTITY column in the "parent" table? If not, just define that column with the int datatype and move the data with the existing values. If so, seed the identity to the maximum existing value in the source "parent" table plus one and then SET IDENTITY_INSERT ON for the destination "parent" table, insert the data (with the existing values from the source table), and then SET IDENTITY_INSERT OFF for the destination table. Either way, you can keep all the FK values and relationships intact.
Jason Wolfkill
May 29, 2013 at 11:01 am
AndrewSQLDBA (5/28/2013)
Good Morning EveryoneI hope that everyone had a very nice weekend.
I am inserting rows from one database to another. The tables that I am using as the source, has an Identity column that is related and has foreign key constraints to each table that I want to use. I have the parent table in the destination filled with data. What would be the best and most efficient way to insert the other data based on the Identity column of the parent table? I would rather not use the IN clause. I am thinking that a JOIN would be the most efficient.
I am using SELECT * INTO <DestinationDatabase>dbo.<TableName>
FROM <SourceDatabase>.dbo.<TableName>
so that SQL Server will create the tables for me.
I appreciate any and all comments, assistance and suggestions.
Thank you in advance
Andrew SQLDBA
Also, the SELECT . . . INTO method of creating the tables will not propagate the foreign key constraints on the source tables to the destination tables it creates.
Jason Wolfkill
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply