SQL Server: get auto incrementing id inside stored procedure?

Question

My database has a parent table with an auto-increment primary key identifier identifier and a regular TIMESTAMP column. I have child tables with a foreign key that refer to the parent's "ID" column.

I want to write a stored procedure that inserts a new column into the parent and child databases. How would I set the “ID” column of a child to fit the new “ID” column with auto-increment? This requires a separate:

SELECT TOP 1 * FROM PARENT_TABLE 

Or is there another way?

Answer 1

You can get it from SCOPE_IDENITY (). For instance:

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

Answer 2

select scope_identity ();