How to get ID from one table and associate with record in another table in SQL Server

Question

I've tried searching for the answer to this one to no avail. There is no good logic behind the way this was setup. The guy does not know what he's doing, but it's what I have to work with (long story).

I'm using SQL Server 2008R2 I need to take records from one table and transfer the data to 4 separate tables all with a one to one relationship (I know - not smart). I need to get the value from the Identity field in the first table the data is inserted into, then populate the other 3 tables with the same ID and disperse the data accordingly. for example:

OldTable: Field1, Field2, Field3, Field4

NewTable1: Identity field, Field1 NewTable2: ID, Field2 NewTable3: ID, Field3 NewTable4: ID, Field4

I'd like to handle this in a stored procedure. I'd like to do a loop, but I read that loops in SQL are inadvisable.

Loop moving through each record in OldTable... (??)

INSERT INTO NewTable1
  (Field1)
Select Field1 from OldTable

   INSERT INTO NewTable2
     (ID, Field2)
   Select SCOPE_IDENTITY?, Field2 From OldTable Where OldTable.ID = ??

   etc for other 2 tables

Loop to next record in OldTable

I am not sure how to use SCOPE_IDENTITY, but I have a feeling this will be involved in how I accomplish this.

Also, I'm probably going to need to setup a trigger for whenever a new record is created in NewTable1. I know, it's insanity, but I can't do anything about it, just have to work around it.

So, I need to know 1: the best way to initially populate the tables 2: how to make triggers for new records

The solution to 1 might involve 2.

Please help!

Is any column or a combination of columns in the OldTable a primary key or unique? — Mikael Eriksson, Sep 05 '13 at 15:55
Yes, I could use that PK to associate them, I suppose. That field is not stored in the new tables, though. What would be a good way I could use it? — user2751304, Sep 05 '13 at 15:58

score 2 · Answer 1 · edited May 23 '17 at 12:06

You can use the output clause of the merge statement to get a mapping between the existing primary key in OldTable and the newly generated identity ID in NewTable1.

-- Temp table to hold the mapping between OldID and ID
create table #ID
(
  OldID int primary key,
  ID int
);

-- Add rows to NewTable1 and capture the ID's in #ID
merge NewTable1 as T
using OldTable as S
on 1 = 0
when not matched by target then
  insert(Field1) values(S.Field1)
output S.ID, inserted.ID into #ID(OldID, ID);

-- Add rows to NewTable2 using #ID to get the correct value for each row
insert into NewTable2(ID, Field2)
select I.ID, O.Field2
from #ID as I
  inner join OldTable as O
    on I.OldID = O.ID

insert into NewTable3(ID, Field3)
select I.ID, O.Field3
from #ID as I
  inner join OldTable as O
    on I.OldID = O.ID

insert into NewTable4(ID, Field4)
select I.ID, O.Field4
from #ID as I
  inner join OldTable as O
    on I.OldID = O.ID

drop table #ID;

SQL Fiddle

See also Using merge..output to get mapping between source.id and target.id

score 0 · Answer 2 · answered Sep 05 '13 at 16:07

How about using the OUTPUT clause of the insert statement? Assuming that Field1 is a unique key on the OldTable...

Declare @IDinserted table(ID int, Field1 varchar(255));

Insert Into NewTable1(Field1)
Output inserted.ID, inserted.Field1 into @IDinserted
Select OldID, Field1 from OldTable;

Insert Into NewTable2(RowID, Field2)
Select i.ID, o.@Field2 
from @IDinserted i Inner Join OldTable o
  on i.Field1=o.Field1;

How to get ID from one table and associate with record in another table in SQL Server

2 Answers2