How do I use an INSERT statement's OUTPUT clause to get the identity value?
You can either have the newly inserted ID being output to the SSMS console like this:
INSERT INTO MyTable(Name, Address, PhoneNo)OUTPUT INSERTED.IDVALUES ('Yatrix', '1234 Address Stuff', '1112223333')You can use this also from e.g. C#, when you need to get the ID back to your calling app - just execute the SQL query with .ExecuteScalar() (instead of .ExecuteNonQuery()) to read the resulting ID back.
Or if you need to capture the newly inserted ID inside T-SQL (e.g. for later further processing), you need to create a table variable:
DECLARE @OutputTbl TABLE (ID INT)INSERT INTO MyTable(Name, Address, PhoneNo)OUTPUT INSERTED.ID INTO @OutputTbl(ID)VALUES ('Yatrix', '1234 Address Stuff', '1112223333')This way, you can put multiple values into @OutputTbl and do further processing on those. You could also use a "regular" temporary table (#temp) or even a "real" persistent table as your "output target" here.