database - Returning a value from an INSERT statement in SQL Server 2008

Question

Database : SQL Server 2008

Is there a way to have an insert statement return a value in SQL Server,

There is a similar question in

Return a value from a insert statement , which talks about ORACLE .

I don't work with databases much, and not aware of ORACLE/SQL Server much, so sorry to ask it all over again.

For simplicity let me provide a small example :

The table EmpDetails has EmpiD and EmpName. The value of EmpID is autogenerated by the database.

INSERT INTO EMPDETAILS VALUES("John")

Now I want to get the value of EmpID associated with John

I don't want a stored procedure, I want a SQL statement only .

Answer 1

Yes - you can use the little known and little used OUTPUT clause in your INSERT statement

INSERT INTO dbo.YourTable(col1, col2, col3, ...., ColN)
OUTPUT Inserted.Col1, Inserted.Col5, Inserted.ColN
VALUES(val1, val2, val3, ....., valN)

This returns a normal set of data, that you can deal with as you need to.

As the MSDN docs show, you can also send the OUTPUT values into e.g. a table variable or temp table for later use, if you need to.

To answer your updated question, use this:

INSERT INTO dbo.EMPDETAILS(EmpName)
OUTPUT Inserted.EmpID
VALUES("John")