0

I have a temporary table derived from a number of tables, which needs to be inserted into two tables (T1 and T2). T1 has a primary key (auto generated), which must be inserted into T2 as a foreign key (One : Many relationship)

I know if I used the following insert statement

INSERT INTO T1 (.....)
SELECT (.....) FROM X

I am unable to use the scope_identity, as that would only give the me last auto generated ID, to use in T2.

Other than either using a curser or looping though each row, what options exist, for ensuring the relationships between records split across tables remain? Just for information, this insert process happens regularly, and could included up 1000+ records across the two tables.

  • Have a look at [this question](http://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id). Looks like what you are looking for. – Mikael Eriksson May 20 '12 at 13:11

1 Answers1

2

The "Output clause" can solve your problem I think. An example 

create table itest ( i int identity not null primary key, j int not null unique )
create table #new ( i int not null, j int not null)
insert into itest (j)
output inserted.i, inserted.j into #new
select o.object_id from sys.objects as o
select * from #new
drop table #new, itest;
go
buckley
  • 13,690
  • 3
  • 53
  • 61
  • A good read on the subject can be found here http://www.simple-talk.com/sql/learn-sql-server/implementing-the-output-clause-in-sql-server-2008/ – buckley May 20 '12 at 12:36