SQL Server: Insert rows in a table and at the same time retrieve the id inserted with only a single statement? the OUTPUT and the OUTPUT INTO clauses

Hello friends!

Two or three months ago we talk about How to get the Last Inserted Identity value

You know that our goal is to find always the fastest method. According to that post we found that the fastest ways is to use the @@IDENTITY, SCOPE_IDENTITY or IDENT_CURRENT(T) commands:

So a good way to insert rows in a table and retrieve the last identity is this:

Oh yes! Cool! but we need again 2 T-SQL commands!

How to improve?

The OUTPUT and the OUTPUT INTO clauses

"It's easy and plan"

For the example create a table named Product and a clustered index:

Create table Product (id int identity(1,1), codice varchar(20))
Create clustered index pk_Product on Product(id)

Then execute the following statements:

Insert into Product (codice)
OUTPUT inserted.id
Values ('A') 

You will immediately noticed that the T-SQL command return the id inserted:

The simply tips is the OUTPUT clause.

Very helpfuf is also the OUTPUT INTO clause:

Declare @val table (id int)
Insert into Product (codice)
OUTPUT inserted.id INTO @val
Values ('AAA'),('BBB')

Select * from @val

Using the OUTPUT INTO clause you will be able to insert the id of the rows inserted for example in a table variable.

 

Once we have the data into a table variable (@val) we do everything with them. With this method we are sure to check data that are really inserted.

Note that: 

• the OUTPUT and OUTPUT INTO clauses are available also the DELETE statement
• You can specify after the INSERTED token every field of your table, not only the ID field.
  

That's all for today!
Subscribe if you found this post helpful and ... wait for the next post!

Thank you,
Luca

Previous post: Sql Server: The execution plan and the cost of the operators inside it. The clustered index scan operator. Part 1.