0

We are trying to merge two databases and keep track of foreign key constraints. I'm having trouble putting into words exactly where I am running into issues, so please forgive my explanation by example:

Suppose we have two tables, Table1 and Table2. Each table has identical structure. For simplicity, let's say:

Table1(Key1 INT IDENTITY(1,1), MyVal VARCHAR(50))
Table2(Key2 INT IDENTITY(1,1), MyVal VARCHAR(50))

There are other tables that have foreign key references on Key1 and Key2. So, after I insert from Table1 into Table2, I want to keep track of the old key and the new key so that I can use this information for foreign keys later. So, I am trying something like this:

DECLARE @KeyMatch TABLE(Key1 INT,Key2 INT)

INSERT INTO Table2(MyVal)
OUTPUT Table1.Key1, inserted.Key2 INTO @KeyMatch(Key1,Key2)
SELECT MyVal
FROM Table1
INNER JOIN SomeOtherTable ON Table1.MyValue LIKE SomeOtherTable.MyValue

But, this does not work. Key1 is not a valid OUTPUT. Is there a standard way to map old keys to new keys? Do I need to use a cursor and loop through to manually generate the lookup table? I would like to avoid the cursor if I can. I also thought of creating a temp table that would include just the data I want to join, have that be auto-numbered, and then output to an auto-numbered lookup table so the auto-numbers would match, but I am worried about possible deletions in SomeOtherTable which is a table with high transaction volume potentially affecting the number of records that would be returned between the initial select and the insert into the actual table.

SlipEternal
  • 222
  • 2
  • 12
  • 2
    yes, you can use the MERGE trick. it allows to output all participating columns. SOmething like ;merge target using source ON 1 = 0 when not matched by target insert ... output inserted.id, source.id -- – siggemannen Apr 26 '23 at 15:56
  • An obvious way without temp tables is: 1) add a new column to the target table for "old key" and insert rows from the old table with the old key in it. 2) Drop foreign keys in any tables that used the old key, old table; 3) update those tables to use the new key in the target table where the old key (column) matches 4) add a new foreign key to those tables pointing to the new table, new key. 5) drop the "old key" column from the target table. – Zorkolot Apr 26 '23 at 16:15

0 Answers0