Microsoft SQL Server INSERT INTO Use OUTPUT to get the new Id
Example
When INSERTing, you can use OUTPUT INSERTED.ColumnName to get values from the newly inserted row, for example the newly generated Id - useful if you have an IDENTITY column or any sort of default or calculated value.
When programatically calling this (e.g., from ADO.net) you would treat it as a normal query and read the values as if you would've made a SELECT-statement.
-- CREATE TABLE OutputTest ([Id] INT NOT NULL PRIMARY KEY IDENTITY, [Name] NVARCHAR(50))
INSERT INTO OutputTest ([Name])
OUTPUT INSERTED.[Id]
VALUES ('Testing')
If the ID of the recently added row is required inside the same set of query or stored procedure.
-- CREATE a table variable having column with the same datatype of the ID
DECLARE @LastId TABLE ( id int);
INSERT INTO OutputTest ([Name])
OUTPUT INSERTED.[Id] INTO @LastId
VALUES ('Testing')
SELECT id FROM @LastId
-- We can set the value in a variable and use later in procedure
DECLARE @LatestId int = (SELECT id FROM @LastId)
