How to Use an Insert Statement'S Output Clause to Get the Identity Value

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

Insert ... select output clause, get both INSERTED.ID and SELECTED.ID

Found an answer here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8cf1e38e-a29b-4ad7-abc7-b9fe2b987698/insert-into-using-select-with-output-into-multipart-identifier-could-not-be-bound?forum=transactsql

Tom:

You can't use columns from the SELECT part of an INSERT ... SELECT in
the OUTPUT clause. The only columns an INSERT statement can use in
the OUTPUT clause is columns in the inserted pseudo table.
Fortunately, there is a way around this restriction. Use MERGE to
simulate an INSERT by using an ON condition that is always false.

Here is the code:

select * 
into #temp
from [dbo].[MindmapNodes]
where [MindmapId] = 215

DECLARE @mindmapNodes table(Id int, OldId int);
MERGE INTO [dbo].[MindmapNodes]
USING #temp AS cf
ON 1= 0
WHEN NOT MATCHED THEN
  INSERT
  ( [MindmapId],
    [Loc],
    [Title],
    [SnippetId]
  ) 
  Values
  (
    216,
    cf.Loc,
    cf.Title,
    cf.SnippetId
  )
Output inserted.[Id], cf.[Id] INTO @mindmapNodes;

Using OUTPUT INTO with from_table_name in an INSERT statement

No, because an INSERT doesn't have a FROM; it has a set of values that are prepared either by the VALUES keyword, or from a query (and even though that query has a FROM, you should conceive that it's already been run and turned into a block of values by the time the insert is done; there is no s.code any more)

If you want to output something from the table that drove the insert you'll need to use a merge statement that never matches any records (so it's only inserting) instead, or perhaps insert all your data into @tmp and then insert from @tmp into the real table - @tmp will thus still be the record of rows that were inserted, it's just that it was created to drive the insert rather than as a consequence of it (caveats that it wouldn't contain calculated columns)

T-SQL : How can I retrieve the OUTPUT from an INSERT statement with PEAR DB?

Finally, I just did a simple INSERT by removing the OUTPUT INSERTED.ID_TICKET AS lastId line.
I then did a second query() call immediately after : SELECT @@IDENTITY as lastId and it now gives the desired results.

Pretty much what Álvaro González suggested.

Now, following Dan Guzman sugestion and adding SET NOCOUNT ON; at the beggining of my original INSERT with the OUTPUT clause indeed solved the problem !
I now get a result set with a unique row and column lastId as desired.
My hat off to both of you.

Output Inserted T-SQL

The typical code is:

declare @ids table (id int);

insert into [dbo].[TEP_Payments_Table] ([col1], [col2]) 
    output inserted.id into @ids
    values ('testval1', 'testval2');

Your version would probably work if the output clause were before the values clause.

Using SQL OUTPUT for INSERT into a table with Identity (or default values)

You just need to specify which columns in Payments to insert into:

UPDATE [SequenceNumbers] 
SET Sequence = Sequence + 1 
  OUTPUT Inserted.EstateId, Inserted.SequenceNo
  INTO Payments(EstateId, SequenceNo)     -- define what columns to insert into!
WHERE EstateId = @EstateId

---

Related Topics