How do I use an INSERT statement’s OUTPUT clause to get the identity value?

by

You can either have the newly inserted ID being output to the SSMS console like this:

INSERT INTO MyTable(Name, Address, PhoneNo)
OUTPUT INSERTED.ID
VALUES ('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.

More Related Contents:

  1. Insert results of a stored procedure into a temporary table
  2. How important is the order of columns in indexes?
  3. Using the result of an expression (e.g. Function call) in a stored procedure parameter list?
  4. SQL Server – transactions roll back on error?
  5. Difference between a User and a Login in SQL Server
  6. Exporting data In SQL Server as INSERT INTO
  7. Convert Xml to Table SQL Server
  8. Fastest way to update 120 Million records
  9. For Nvarchar(Max) I am only getting 4000 characters in TSQL?
  10. How to find current transaction level?
  11. How do I group on continuous ranges
  12. INSERT rows into multiple tables in a single query, selecting from an involved table
  13. “select * from table” vs “select colA, colB, etc. from table” interesting behaviour in SQL Server 2005
  14. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  15. Extracting hours from a DateTime (SQL Server 2005)
  16. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  17. SQL Server 2008 – Help writing simple INSERT Trigger
  18. SQL Server PIVOT perhaps?
  19. postgresql: INSERT INTO … (SELECT * …)
  20. How to find consecutive rows based on the value of a column?
  21. Custom aggregate function (concat) in SQL Server
  22. How to escape square brackets inside square brackets for field name
  23. How do you copy a record in a SQL table but swap out the unique id of the new row?
  24. What is the difference between a stored procedure and a view?
  25. How to change column order in a table using sql query in sql server 2005?
  26. Is there a quick way to check if ANY column is NULL?
  27. Why do SQL Server Scalar-valued functions get slower?
  28. How to bulk insert only new rows in PostreSQL
  29. SQL query for parent-child chain
  30. Querying Active Directory from SQL Server 2005

Leave a Comment