Creating a Cross-Reference table with the OUTPUT clause

Question

Creating a Cross-Reference table with the OUTPUT clause

BrainDonor

SSCoach

Points: 19254
More actions
April 5, 2011 at 9:52 am

#248976

I'm currently moving data from one database to another, and as the Identities change I need to create a table to track these, so I can update the foreign keys of associated tables as they move across too.
The OUPUT clause goes some way towards this, as I can write the new IDs to a table as the data is inserted into the new table, but I also need the ID of the 'source' table.
I can write this 'tracking' table by controlling this move using a cursor, but that is painfully slow.
So I'm after ideas that can do what I require without the use of a cursor. I haven't been able to find anything suitable, but I'm prepared to believe that I'm missing the obvious.
Example Code (all in tempdb, but in the real world 'Source' and 'Target' are tables in seperate databases) just to get the concept across, and to give others something to play with:
USE tempdb;
GO
CREATE TABLE Target_Table(
TT_IDINT IDENTITY,
TT_TextVarChar(20)
);
CREATE TABLE Source_Table(
ST_IDINT IDENTITY,
ST_TextVarChar(20)
);
CREATE TABLE Inserted_Data(
New_IDINT,
Old_IDINT
)
INSERT INTO Target_Table(TT_Text)
VALUES('Target One');
INSERT INTO Source_Table(ST_Text)
SELECT('Source One')
UNION ALL
SELECT('Source Two')
UNION ALL
SELECT('Source Three');
SELECT * FROM Target_Table;
SELECT * FROM Source_Table;
INSERT INTO Target_Table(TT_Text)
OUTPUT INSERTED.TT_ID
INTO Inserted_Data(New_ID)
SELECT ST_Text
FROM Source_Table;
SELECT * FROM Inserted_Data;
SELECT * FROM Target_Table;
DROP TABLE Target_Table;
DROP TABLE Source_Table;
DROP TABLE Inserted_Data;
'Inserted_Data' contains the list of the new IDs, but what I really want is the ID from the 'Source_Table' alongside, so then I know the 'from' and 'to' IDs for later use.
Thanks for any assistance.
Steve Hall
Linkedin
Blog Site

Viewing 3 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

any chance of changing the schema? at least adding a column to be removed after the data transfer?

whenever I've done a data migration like this, I've always added a column to the Target table to hold the Original/old PKID; so then i inserted oldPKID,othercolumns into TargetTable;

so i had both the old an d new IDs in hte key table itself(instead of a temp table), which i really needed for child FK constraint migration on subsequent tables.

after it was all over, and we confirmed the data was clean, I could drop the column for the OldPKID column.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

BrainDonor SSCoach Points: 19254 More actions · Answer 2

As I said - I'm prepared to believe I'm missing the obvious...

The idea did briefly cross my mind at some point, but I was fixated on trying to do this all in one pass, whereas this method would take two passes (insert the rows and then read the IDs). But so what? It's going to be a good deal faster then anything a cursor could manage.

Thanks for your time.

Steve Hall
Linkedin
Blog Site