TSQL OUTPUT Clause on Insert Statements
One of the often overlooked features of SQL Server is the OUTPUT clause, which applies to INSERT, UPDATE, DELETE, and MERGE statements. In this post we are going to go over the basics of using the OUTPUT clause on an INSERT statement.
What the output clause does is return a result set on queries that don’t usually return a result set like INSERT, UPDATE, DELETE, and MERGE. You can have the INSERT statement return the full row that was inserted, including any columns that were filled in automatically, for instance with default values, or with IDENTITY values.
The output clause was added to Microsoft SQL Server in version 2005, and is available in all editions including Express, Standard, Enterprise, Developer and Azure.
Lets take a look at how it works. First we will set up a sample database to work with. Caution: this script created a database called QueryTraining on your SQL Server. If you don’t wish do create the database, or don’t have permissions to do so, just create the table, and work in your existing database.
USE Master;
GO
CREATE DATABASE [QueryTraining];
GO
USE [QueryTraining];
CREATE TABLE [dbo].[Inventory](
[InventoryID] [INTEGER] IDENTITY(-2147483647,1) PRIMARY KEY,
[Category] [VARCHAR](200),
[ItemSKU] [VARCHAR](200),
[Description] [VARCHAR](4000),
[QuantityOnHand] [INT],
[UnitCost] DECIMAL(10,2),
[UnitRetail] DECIMAL(10,2),
[Archived] BIT
);
INSERT INTO dbo.Inventory (Category, ItemSKU, Description, QuantityOnHand,
UnitCost, UnitRetail, Archived)
VALUES
('SLEEPINGBAG', 'SB001-LG-RED', 'Sleeping Bag - Large - RED', 5, 5.75, 19.99, 0),
('SLEEPINGBAG', 'SB001-LG-GRN', 'Sleeping Bag - Large - GREEN', 13, 5.75, 14.99, 1),
('SLEEPINGBAG', 'SB001-LG-BLK', 'Sleeping Bag - Large - BLACK', 1, 5.75, 19.99, 0),
('TENT', 'TT001-10-CMO', 'Tent - 10 Man - CAMO', 5, 10.00, 99.95, 0),
('AXE', 'AX013-HT-BLK', 'Axe - Hatchet - Black', 35, 4.99, 24.95, 0),
('FOOD', 'MR005-1S-SPG', 'MRE - 1 Serve - Spaghetti', 5, 0.50, 7.99, 0),
('FOOD', 'MR006-1A-PIZ', 'Pizza', 1, NULL, 9.95, 0);
If you are wondering why the IDENTITY is seeded with the large negative value, see my article on this topic.
Now that the database is set up, we will start with a basic insert statement, without the output clause.
INSERT INTO dbo.Inventory (Category, ItemSKU, Description,
QuantityOnHand, UnitCost, UnitRetail, Archived)
VALUES
('FOOD', 'MR009-210-ICE', 'MRE - Ice Cream - Chocolate', 1, NULL, 11.95, 0);
Which produces the following basic output:
Now we will modify the query and simply add the following before the VALUES keyword:
OUTPUT INSERTED.*
So the query ends up looking like this:
INSERT INTO dbo.Inventory (Category, ItemSKU, Description,
QuantityOnHand, UnitCost, UnitRetail, Archived)
OUTPUT INSERTED.*
VALUES
('FOOD', 'MR009-209-ICE', 'MRE - Ice Cream - Rocky Road', 1, NULL, 11.95, 0);
Now we get the following output:
When scrolled to the right, you can see the entire row that was inserted was returned, plus the InventoryID which came from the IDENTITY value is returned also. This result set can be processed in code like any result set returned from a typical SELECT statement.
What if I just want the IDENTITY value that was inserted.
You just change the OUTPUT clause instead of INSERTED.* you ask for INSERTED.InventoryID (or whatever your IDENTITY column is called).
INSERT INTO dbo.Inventory (Category, ItemSKU, Description,
QuantityOnHand, UnitCost, UnitRetail, Archived)
OUTPUT INSERTED.[InventoryID]
VALUES
('FOOD', 'MR009-211-ICE', 'MRE - Ice Cream - Vanilla', 1, NULL, 11.95, 0);
When run you can see just a single column with the InventoryID returned in the result set.
Technically at this point there are many other ways to get the inserted ID, including @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT, and various other methods that people have creatively come up with over time.
Multiple Values
Now, how about returning multiple values, that’s something that you can’t get with IDENTITY, SCOPE_IDENTITY, or IDENT_CURRENT.
Let’s take a look.
INSERT INTO dbo.Inventory (Category, ItemSKU, Description,
QuantityOnHand, UnitCost, UnitRetail, Archived)
OUTPUT INSERTED.[InventoryID]
VALUES
('FOOD', 'MR009-212-ICE', 'MRE - Ice Cream - Cookies and Cream', 1, NULL, 11.95, 0),
('FOOD', 'MR009-213-ICE', 'MRE - Ice Cream - Chocolate Chip', 1, NULL, 11.95, 0);
Nothing changes on the OUTPUT line, the only change is the addition of a second row when inserting the values. By the way this format for insert requires SQL Server 2008 or newer, it doesn’t work on SQL Server 2005.
Here is what we get back:
There you have it. The basic overview of the OUTPUT clause on INSERT statements.
See Also
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
Leave a Reply