CODEWITHSUNDEEP
sqlsql-servert-sql
Oday Salim
Oday Salim

Reputation: 1147

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:

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.

Upvotes: 3

Views: 9664

Answers (2)

Marcin Sulecki
Marcin Sulecki

Reputation: 174

To return just the ID to your application, you can do it much simpler:

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

For example, if you use C# and Dapper

var id = db.QuerySingle<int>(sql, payment);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269703

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.

Upvotes: 8

Related Questions