OUTPUT clause – the SQL developers panacea.

A very frequent requirement in a database, especially in an OLTP system is to be able to capture the identity and values of the last inserted record in a table so that we can use it to populate the foreign key in a child table and any other tables that require it.

It is easy enough to capture the last inserted identity if we are only dealing one record at a time using @@IDENTITY, but what happens when we need to do batch inserts? Well, the old way of doing things would mean you had the following options:

  • Execute a loop (either a cursor or a WHILE statement), and for each iteration we’d write a record in each of the child tables
  • You’d have to select the same data twice, once to insert the audit records and once to update the data
  • You’d rely on a trigger to write to the audit table

Well that should now not be necessary as using the OUTPUT clause we can capture all the data we’ve inserted, updated or deleted in a temporary area for use later on, or say in an auditing scenario, we can output the data directly to the auditing table. More importantly we can use that data in a set based transaction instead of row based. The advantages here are obvious. 

Amazon Link: Sams Teach Yourself Microsoft SQL Server T-SQL in 10 Minutes

I have created an example script using a scenario of a website that collects a customers name details and stores their username and password in a separate table. Lets also say that the customer table is also used by other applications, so not every customer has a logon. Finally because there are so many users a batch is run every 10 minutes to generate a password which the website user can change later on.

Whilst this scenario is fairly unlikely, it does demonstrate the OUTPUT clause quite nicely.


USE [YourDatabase]

IF EXISTS( SELECT 1 FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Customer'
AND TABLE_TYPE = 'BASE TABLE'
AND TABLE_SCHEMA = 'dbo')
DROP TABLE dbo.Customer
GO

/* This will be our master customer table */
CREATE TABLE dbo.Customer ( CustomerId INT IDENTITY NOT NULL,
Title NVARCHAR(10) NOT NULL,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
Gender NVARCHAR(1) NOT NULL,
UserName NVARCHAR(20) NOT NULL )

IF EXISTS( SELECT 1 FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'CustomerLogin'
AND TABLE_TYPE = 'BASE TABLE'
AND TABLE_SCHEMA = 'dbo')
DROP TABLE dbo.CustomerLogin
GO

/* This is our website table to authenticate our customers */
CREATE TABLE dbo.CustomerLogin( CustomerId INT NOT NULL,
UserName NVARCHAR(20) NOT NULL,
UserPassword NVARCHAR(40) NOT NULL )

/* Table variable to hold resultset */
DECLARE @TempCustomer TABLE ( CustomerId INT NOT NULL,
UserName NVARCHAR(20) NOT NULL )

/* Insert our web customers names into the main table */
INSERT INTO dbo.Customer (Title, FirstName, LastName, Gender, UserName)

-- Store the inserted ids and username for later use
OUTPUT Inserted.CustomerId,
Inserted.UserName
INTO @TempCustomer

-- Some example customers
SELECT 'Captain', 'Donald', 'Duck', 'M', 'FlyingDuck'
UNION ALL
SELECT 'Mr', 'Darth', 'Vader', 'M', 'DarkLord'
UNION ALL
SELECT 'Miss', 'Minnie', 'Mouse', 'F', 'BigEars'
UNION ALL
SELECT 'Father', 'Abraham', '', 'M', 'PreacherMan'

-- Insert stored Id and username. Generate a password
INSERT INTO dbo.CustomerLogin (CustomerId, UserName, UserPassword)
SELECT CustomerId,
UserName,
NEWID() -- Interesting password I know but you get the idea ;-)
FROM @TempCustomer

/* Our website table now contains the identities
created during the master table insert */
SELECT *

FROM dbo.Customer C

JOIN dbo.CustomerLogin CL
ON C.CustomerId = CL.CustomerId

Note that as well as having the INSERTED.* syntax for inserts (and replacement values in updates), you also have DELETED.* which returns deletes or values replaced in an update.

There you have it. The OUTPUT clause.

Cheers
Frank

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.