Search code examples
sqlsql-servert-sql

Output Inserted T-SQL

This might be a noob question, but I am very much not familiar with how the OUTPUT clause of an INSERT works in SQL Server.

Problem

I am inserting values into a table and I would like to generate a unique id for that operation. Previously I used the following code to generate a unique id:

select top 1 id 
from [dbo].[TEP_Payments_Table] 
order by id desc + 1

The issue with this is some users can have duplicate id's if they use the form at the same time.

Solution

With much research, I came across Output Inserted that apparently solves this problem.

To do this, I have created the following table:

  • TEP_ReceiptID - where column ReceiptID is id col and primary key.

I then attempted to use "Output Inserted" into my usual Insert statement, code:

INSERT INTO [dbo].[TEP_Payments_Table] ([col1], [col2]) 
VALUES
OUTPUT inserted.ID INTO dbo.TEP_ReceiptID
('testval1', 'testval2')

To my disappointment, this does not work. Again, my fault as I am not familiar with this Output syntax.

I would very much appreciate it if you inform me where I have gone wrong.

Solution

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