Using INSERT OUTPUT in a SQL Server Transaction

By:   |   Comments (6)   |   Related: > TSQL


Problem

Frequently I find myself in situations where I need to insert records into a table in a set-based operation wrapped inside of a transaction where secondarily, and within the same transaction, I spawn-off subsequent inserts into related tables where I need to pass-in key values that were the outcome of the initial INSERT command. Thanks to a Transact/SQL enhancement in SQL Server, this just became much easier and can be done in a single statement... WITHOUT A TRIGGER!

Solution

One of the Transact/SQL enhancements in Microsoft SQL Server is the OUTPUT sub-clause of the INSERT statement. You can now capture the records inserted via an INSERT statement (think also being able to capture IDENTITY column values for the new rows) for subsequent use in an additional INSERT statement for a child table to persist referential integrity without the need for an INSERT trigger.

Why not just use a trigger? It's a viable and proven construct of SQL Server, right?

The short answer is "Yes, it is." However, triggers are one of those nasty little secrets that the database keeps. They don't just jump right out at you and say "HERE I AM!" Take for example the troubleshooting process of deadlocks or tuning a poorly-performing query - a trigger sitting in the background performing as it's been asked to may be causing your issues, but you're going to go through many iterations of searching stored procedures, and ad-hoc T/SQL code before you probably even stop to consider there is a trigger firing off data modification language commands (DML) - INSERTS, UPDATES, or DELETES that are adjunct to what you're trying to diagnose. I associate the use of triggers with the use of ad-hoc T/SQL code used in an application's code stack and passed to a SQL Server instance for processing - practices to shy away from.

That is why I like what I see with the INSERT-OUTPUT construct. You get the benefits of being able to capture the inserted values that you can then pass to a secondary command - and you can wrap this all inside a single transaction for atomicity. The syntax for this construct is shown below and differs only slightly from the basic INSERT T/SQL command:

INSERT INTO <SOME_TABLE>
(
<
column_list>
)
OUTPUT INSERTED.<identity_column> --and other columns from SOME_TABLE if need be
INTO <SOME_OTHER_TABLE>
(
<
column_list>
)
SELECT
(
<
column_list>
)
FROM <source_table_OR_JOIN_of_multiple_tables>
WHERE <filtering_criteria>

The only difference between this and a standard INSERT statement is the inclusion on the OUTPUT...INTO statement. To make this easy think of it as simply a secondary INSERT statement inside of the original INSERT statement that captures the values in the virtualized INSERTED table - the same table that a trigger would use - to process a secondary INSERT to another table. In the example below, and in keeping with the holiday season, let's say you're responsible for doing a bit of hiring at the corporate AdventureWorks offices. A right-jolly old elf is being hired for some in-store promotions and in keeping with corporate policy you always perform a 90 day review for any new hires. We want to have the notfication recorded when the new hire is entered without any additional work on the part of Human Resources. The code below demonstates how we can use INSERT-OUTPUT to do this.

USE AdventureWorks;
GO

---Create Example Tables
/*
Note, this is not fully-normalized. I would have included another table
for Notification Types if this was an actual solution.
I would also use an int NotificationTypeID column in Notifications table
instead of a varchar(xx) NotificationType column.
*/

CREATE SCHEMA [HR] AUTHORIZATION dbo;
GO

CREATE TABLE [HR].[Staff]
(
[StaffID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] VARCHAR(30) NOT NULL,
[LastName] VARCHAR(30) NOT NULL,

CONSTRAINT [PK_StaffID] PRIMARY KEY CLUSTERED
(
[StaffID] ASC
)ON [PRIMARY]
) ON [PRIMARY];

CREATE TABLE [HR].[Notification]
(
[NotificationID] [int] IDENTITY(1,1) NOT NULL,
[StaffID] [int] NOT NULL,
[NotificationDate] DATETIME NOT NULL,
[NotificationType] VARCHAR(30) NOT NULL,

CONSTRAINT [PK_NotificationID] PRIMARY KEY CLUSTERED
(
[NotificationID] ASC
)ON [PRIMARY]
) ON [PRIMARY];

Now that we've built the objects for this little exercise we can look at the INSERT-OUTPUT construct in action...

/*
Demonstrate how you can insert the key values added to Staff.StaffID
into Notifications.StaffID in single transaction
*/

INSERT INTO HR.Staff ( FirstName, LastName )
OUTPUT INSERTED.StaffID, DATEADD(d,90,GETDATE()),'90-Day Review'
INTO HR.Notification
(
StaffID,
NotificationDate,
NotificationType
)
VALUES ( 'Santa','Claus');

Selecting now from both the Staff and Notification tables you'll see that the key values were successfully entered into both tables:

SELECT * FROM HR.Staff;
SELECT * FROM HR.Notification;

the Transact/SQL enhancements in Microsoft SQL Server is the OUTPUT sub-clause of the INSERT statement

Now there is a very important - and quite limiting caveat to using INSERT-OUTPUT. The Output target can't be part of any foreign key relationship. Even if there is no cascading relationship to any other object via that relationship in the database. Let's look at what happens if it is. We'll add a foreign key to Notification on StaffID, referencing the StaffID column in the Staff table and then try to add some additional holiday help:

--Add Foreign Key for StaffID column to Notifications table
ALTER TABLE HR.Notification ADD CONSTRAINT [FK_Notification_Staff]
FOREIGN KEY
(
StaffID
)
REFERENCES HR.Staff
(
StaffID
);

/*
Demonstrate how you can insert the key values added to Staff.StaffID
into Notifications.StaffID in single transaction
*/

INSERT INTO HR.Staff ( FirstName, LastName )
OUTPUT INSERTED.StaffID, DATEADD(d,90,GETDATE()),'90-Day Review'
INTO HR.Notification
(
StaffID,
NotificationDate,
NotificationType
)
VALUES ( 'Frosty','Snowman');

SELECT * FROM HR.Staff;
SELECT * FROM HR.Notification;

The following error message is returned as expected:

Msg 332, LEVEL 16, State 1, Line 17
The target
TABLE 'HR.Notification' OF the OUTPUT INTO clause cannot be ON either side OF a (PRIMARY KEY, FOREIGN KEY) relationship. Found reference CONSTRAINT 'FK_Notification_Staff'.

This is probably good in this case since chances are good Mr. Snowman is not going to be around in 90 days.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Monday, February 4, 2013 - 4:46:54 PM - Yafos Back To Top (21907)

I have the same question as Xavier above but I dont see an answer. Sorry if I am overlooking.

I would also like to be able to use a table other than the inserted table or a constant (as above).

He gave a good psuedo code example:

  • "This is great for using with an identity key. However I would like to be able to associate that identity with another field I will be entering into a second table that is related to where the identity came from.
  •  Pseudo code example
  •  output inserterd.identityID, uniquekey (from another table associated with the record of the current identityID) in #temp1"
Thank you

Wednesday, December 12, 2012 - 10:03:09 AM - Jeremy Kadlec Back To Top (20914)

ZenCoder,

Have you checked out this tip?

http://www.mssqltips.com/sqlservertip/1385/properly-capturing-identity-values-in-a-sql-server-database/

Thank you,
Jeremy Kadlec
Co-Community Leader


Tuesday, December 11, 2012 - 8:33:29 PM - zencoder Back To Top (20899)

You mentioned 'Transaction', and yet there is no sign of any transaction in your code. i was looking for inserting into related tables with an actual transaction, so as tog et the parent ID for sibsequent related tables.


Friday, October 26, 2012 - 1:35:01 PM - Xavier Back To Top (20107)

This is great for using with an identity key. However I would like to be able to associate that identity with another field I will be entering into a second table that is related to where the identity came from.

 

Pseudo code example

 

output inserterd.identityID, uniquekey (from another table associated with the record of the current identityID) in #temp1

 


Monday, December 13, 2010 - 10:28:38 AM - MK Back To Top (10452)

I am reluctant to give up the foreign key relationship. 

In order to keep the foreign key, and possibly solve Danijel's two audit tables, you could write to a temp table/ table variable.  Not sure if it's better than a trigger, but it is another option.

BEGIN TRAN
DECLARE @tmpNotification TABLE (
     StaffID INT
     , NotificationDate DATETIME
     , NotificationType VARCHAR(30)
);
INSERT INTO HR.Staff ( FirstName, LastName )
OUTPUT INSERTED.StaffID, DATEADD(d,90,GETDATE()),'90-Day Review'
INTO @tmpNotification
    (
        StaffID,
        NotificationDate,
        NotificationType
    )
VALUES  ( 'Frosty','Snowman');

INSERT INTO HR.Notification
SELECT * FROM @tmpNotification;

INSERT INTO audittable
SELECT * FROM @tmpNotification;

COMMIT;

SELECT * FROM HR.Staff;
SELECT * FROM HR.Notification;

 

 


Monday, December 13, 2010 - 2:53:51 AM - Danijel Back To Top (10451)

Hi Tim,

 

From this tip I can see that a record can be inserted in only one additional table.

Now, let's say I have one master table and two audit tables. Whenever I insert a row in the master table, I also want to insert row in the both audit tables in one go. Is it possible to perform insert to multiple tables using OUTPUT clause?

Cheers,

Danijel















get free sql tips
agree to terms