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