0

Well, I have two tables: 

CREATE TABLE Temp(
        TEMP_ID int IDENTITY(1,1) NOT NULL, ... )

CREATE TABLE TEMP1(
    TEMP1_ID int IDENTITY(1,1) NOT NULL, 
    TEMP_ID int, ... )

they are linked with TEMP_ID foreign key. In a stored procedure I need to create tons of Temp and Temp1 rows and update them, so I created a table variable (@TEMP) and I am dealing with it and finally make one big INSERT into Temp. My question is: how can I fill @Temp with correct TEMP_ID's without insert safely from multiple sessions?

Qué Padre
  • 2,005
  • 3
  • 24
  • 39

1 Answers1

0

you can use Scope_Identity() to find out last inserted row. You can use Output clause to find all newly inserted (or updated) rows.

create table #t1
(
    id int primary key identity,
    val int
)


Insert into #t1 (val) 
    output inserted.id, inserted.val
    values (10), (20), (30)
Jānis
  • 2,216
  • 1
  • 17
  • 27
  • in this case after INSERT into Temp from @Temp I can retrieve a list of TEMP_ID's, but how can I fix TEMP_ID's of Temp1 then? – Qué Padre Sep 02 '14 at 06:13
  • @QuéPadre have a look http://stackoverflow.com/questions/6074999/t-sql-insert-original-value-in-output-clause – Jānis Sep 02 '14 at 08:53