SQL Server: Retrieve auto-incremented ID inside a stored procedure?

Question

My database has a parent table with an auto-incrementing primary key identity 'ID', and a normal 'TIMESTAMP column'. I have child tables with a foreign key that refer to the parent 'ID' column.

I want to write a stored procedure that inserts a new column into both the parent and child databases. How would I set the child 'ID' column to equal the new auto-incremented parent 'ID' column? Does this require a separate:

SELECT TOP 1 * FROM PARENT_TABLE

Or is there another way?

Answer 1

You can retrieve it from SCOPE_IDENTITY(). For example:

declare @myid int
INSERT INTO table (field) VALUES ('value')
SELECT @myid = SCOPE_IDENTITY()