SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Returning Results from an Insert – OUTPUT clause

I needed to return an identity value recently from an insert for use in another piece of code. For a client front end, you can easily encapsulate your insert in a stored procedure and then SELECT scope_identity() to get the last identity. However there’s an easier way: the output clause.

The OUTPUT clause is a clause that goes in your INSERT statement and allows you access to the INSERTED table, just like a trigger (also the DELETED table.

A short example below, where data is being added by the server in the state of an identity and a default. I am returning them with the OUTPUT clause.

CREATE TABLE mytesttable
( MyID INT IDENTITY , mychar VARCHAR(20) , mydate DATE DEFAULT GETDATE() ) GO DECLARE @mytable TABLE ( i INT, d DATE); INSERT dbo.mytesttable (mychar) OUTPUT INSERTED.myid, INSERTED.mydate INTO @mytable
VALUES ( 'First Row') SELECT i, d FROM @mytable

There are any number of ways to use this data, especially in terms of logging or inserts into another table. It should be cleaner code, but it doesn’t mean that you should be running inserts from the client without stored procedures, or at least without explicit parameters. Make sure you still use those.

Filed under: Blog Tagged: syndicated, T-SQL

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...