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 about 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.
You can do the same with SQL or TSQL. Just assign the identify column as soon as you do the insert.
Just use
SELECT SCOPE_IDENTITY() [Iden] after the insert statement in the same block. This shud work !
-- 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)
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