Get Identity Field and More From Inserts
Often times when inserting a new record into a table that has an auto-key field (an identity field that is set to auto increment), you may need to get that new ID back to use in other SQL statments. This can be done with @@IDENTITY or SCOPE_IDENTITY(), but those methods are very limited when compared to the new OUTPUT clause in SQL Server 2005. This article will show how and when to use these powerful TSQL features.
The reason why I say that the @@IDENTITY and SCOPE_IDENTITY() options are limited is because 1) they only return numeric fields (so “uniqueidentifier” fields won’t work), 2) they can only return the field marked as the table ‘identity’ (so you can’t get a different field that you may be using as the ID), 3) they cannot give back any other auto-generated data such as a timestamp field. Another huge failing is in the case of multiple inserts in one statement. The @@IDENTITY and SCOPE_IDENTITY() options will only return the last record insereted. So if your insert statement added five records, you would only get the ID of the last one.
Intruducing The OUTPUT Clause
SQL Server 2005 intruduces a really amazing new clause that can be used in DELETE, UPDATE and INSERT statements called ‘OUTPUT’. The syntax is easy to follow as you basically just append it to the end of your statement and treat it like a SELECT. This small bit of SQL will show how to get the ID column back from an INSERT using SCOPE_IDENTITY() compared to the OUTPUT clause.
CREATE TABLE dbo.MicrosoftProducts
(
ID INT NOT NULL IDENTITY (1, 1),
ProductName VARCHAR(50) NOT NULL,
ProductDescription VARCHAR(1000) NOT NULL,
EntryDate DATETIME DEFAULT GETDATE() NOT NULL
)
– This will insert a row and retrieve the ID field using @@IDENTITY.
INSERT INTO dbo.MicrosoftProducts (ProductName, ProductDescription)
VALUES (‘SQL Server 2005′, ‘The best version of SQL ever!’)
SELECT @@IDENTITY
– Now using the OUTPUT clause to get the identity field.
INSERT INTO dbo.MicrosoftProducts (ProductName, ProductDescription)
OUTPUT INSERTED.ID
VALUES (‘SQL Server 2005′, ‘The best version of SQL ever!’)
At first glance, these three examples above may all seem equal. They all take the same amount of code, and they all return the identity field of the record that was just inserted. However, if we change the desired results slightly we can see the major failings of the first two, and we’ll see why the OUTPUT clause is so powerful.
Let’s say instead of just wanting to return the new ID to the user, we want to add the EntryDate field to the results that we send back to the user. With the first two options you would have to store the ID in a variable and re-query the table causing much unneeded overhead and code. Whereas with the OUTPUT clause, getting these results are very simple.
Using @@IDENTITY or SCOPE_IDENTITY()
– We have to hold the new ID in a variable.
DECLARE @NewID INT
INSERT INTO dbo.MicrosoftProducts (ProductName, ProductDescription)
VALUES (‘SQL Server 2005′, ‘The best version of SQL ever!’)
– Now we have to assign that variable with the new ID.
SELECT @NewID = SCOPE_IDENTITY()
– Now we have to re-query the table just to get the two fields.
SELECT ID, EntryDate FROM dbo.MicrosoftProducts WHERE ID = @NewID
Using The OUTPUT Clause
INSERT INTO dbo.MicrosoftProducts (ProductName, ProductDescription)
OUTPUT INSERTED.ID, INSERTED.EntryDate
VALUES (‘SQL Server 2005′, ‘The best version of SQL ever!’)
If that is all the OUTPUT clause could do, it would be worth using. But keep in mind that you can also use it to return multiple results if your insert was more than one record. Another great benefit of the OUTPUT clause is that you can use it for DELETEs and UPDATEs as well. So if you wanted to delete some records based on user input and then show them in a GridView what records have been removed, you could do it in one clean step.
The syntax for UPDATEs is a little different, but even more powerful. There is no “UPDATED” object, but rather you can use “DELETED” or “INSERTED” to represent the list of columns before the change and after the change respectively. Example:
OUTPUT DELETED.ProductName AS TheBefore, INSERTED.ProductName AS TheAfter
Comments