Getting the identity of the string I just inserted

Question

I have two tables that are associated with foreign keys. I need to do multiple inserts in the same table, and then use the identifier column values, which are part of my next insert command. All this has to be done through SQL.

I read about SCOPE_IDENTITY (), but all the examples that I see use ASP / PHP / To do the substitution in the next batch of inserts, I do not have this option.

Any pointers appreciated.

Answer 1

Use

SCOPE_IDENTITY() - Function 

instead

@@IDENTITY Variable - 

- , , (, , ). Scope_Identity , , .

Answer 2

SQl Server 2008 OUTPUT.

DECLARE @output TABLE (myid INT)

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

SELECT * FROM @output

, , , , .

- :

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

Answer 3

SQL TSQL. , .

Answer 4

SELECT SCOPE_IDENTITY() [Iden] 

insert . .

Answer 5

-- 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)