Friday, August 25, 2023

Return the ID of INSERTED Record, MS SQL script

1. returning single ID

INSERT INTO YourTable (Column1, Column2, ...) 

OUTPUT INSERTED.IDColumn VALUES (@Value1, @Value2, ...)

run the query using executeReader or executeScalar 



2. returning multiple ID (integer) in a string with coma

DECLARE @GeneratedIDs AS NVARCHAR(MAX); 

INSERT INTO YourTable (Column1, Column2, ...) 

OUTPUT INSERTED.IDColumn INTO @GeneratedIDs 

VALUES (@Value1, @Value2, ...); 

SELECT STUFF((SELECT ',' + CAST(IDColumn AS NVARCHAR(MAX)) 

FROM @GeneratedIDs FOR XML PATH('')), 1, 1, '') AS ConcatenatedIDs;


run this query using executeReader or executeScaler