Getting the identity of the row I just inserted

I've got two tables that are linked with foreign keys. I need to do a few inserts on one table and then use the identity column values are part of my next batch of insert statements. This all has to be done through SQL.

I've been reading abo开发者_Python百科ut SCOPE_IDENTITY() but all the examples I'm seeing are using ASP/PHP/Whatever to do the substitution in the next batch of inserts, I dont have this option.

Any pointers appreciated.

Use

SCOPE_IDENTITY() - Function 

instead of

@@IDENTITY Variable - 

otherwise you get the result of the last inserted identity - if there is a trigger on the table than does inserting somewhere you get the wrong result back (the value inserted by the trigger and not by your statement). Scope_Identity returns the identity of your statement and that is what you normally want.

IN SQl Server 2008 you can also use the OUTPUT clause.

DECLARE @output TABLE (myid INT)

INSERT mytable (field1, field2)
    OUTPUT inserted.myid INTO @output
VALUES ('test', 'test')

SELECT * FROM @output

What makes this espcially valuable is if you use it to insert a bunch of records instead of one you can get all the identities, You can also return any of the other fields you might need.

something like:

DECLARE @output TABLE (myid INT, field1 datetime)

INSERT mytable (field1, field2)
    OUTPUT inserted.myid, inserted.field1 INTO @output
Select '20100101', field3 from mytable2

SELECT * FROM @output

You can do the same with SQL or TSQL. Just assign the identify column as soon as you do the insert.

-- Variable to hold new Id
Declare @NewId int

--Insert some values in to create a new ID
Insert Into dbo.MyTable1 (Col1)
Values (@NewValue)

-- Grab the new Id and store it in the variable
Select @NewId = Scope_Identity()

--Insert the new Id in to another table
Insert Into dbo.AnotherTable (Col1)
Values (@NewId)

You can use

SELECT SCOPE_IDENTITY() [Iden] 

After the insert statement in the same block. This should work.