Table Variables and OUTPUT clause
At the end of my previous post on the OUTPUT clause I mentioned the fact that the OUTPUT clause can be very useful if you want to see the effects of an INSERT/UPDATE/DELETE or MERGE statement without actually committing the change by rolling back the transaction.
You may think that this is only good to you if you are running the query in Management Studio as the information would not be persisted because of the rollback? For example if I had a Product_Audit table rolling back the transaction would rollback the changes made when inserting into Product_Audit. For example:
CREATE TABLE Product_Audit ( [ProductID] [int] NOT NULL,
[Name] [nvarchar](100) NOT NULL,
[ProductNumber] [nvarchar](25) NOT NULL,
[Colour] [nvarchar](15) NULL,
[FrameSize] smallint ) ;
BEGIN TRANSACTION ;
INSERT INTO Products ( [Name], [ProductNumber], [Colour] )
OUTPUT inserted.[ProductID], inserted.[Name], inserted.[ProductNumber], inserted.[Colour], inserted.[FrameSize] INTO Product_Audit( [ProductID], [Name], [ProductNumber], [Colour], [FrameSize] )
VALUES
( 'Mountain-500 Silver, 24', 'BK-M18S-24', 'Silver'),
( 'Mountain-500 Silver, 28', 'BK-M18S-28', 'Silver'),
( 'Mountain-500 Silver, 32', 'BK-M18S-32', 'Silver') ;
SELECT * FROM Product_Audit ;
ROLLBACK TRANSACTION ;
SELECT * FROM Product_Audit ;
If you run this SQL you will see that the statement after the ROLLBACK returns no data. This is where table variables come in useful!
If you’ve read the topic “table (Transact-SQL)” in books online, you may have missed the last sentence “Also, because table variables have limited scope and are not part of the persistent database, they are not affected by transaction rollbacks.” This means that if I used a table variable and not an actual table, the table variable will still hold the changes after the rollback and I can then store or manipulate it as necessary. e.g.
CREATE TABLE Product_Audit ( [ProductID] [int] NOT NULL,
[Name] [nvarchar](100) NOT NULL,
[ProductNumber] [nvarchar](25) NOT NULL,
[Colour] [nvarchar](15) NULL,
[FrameSize] smallint ) ;
DECLARE @Product_Audit TABLE ( [ProductID] [int] NOT NULL,
[Name] [nvarchar](100) NOT NULL,
[ProductNumber] [nvarchar](25) NOT NULL,
[Colour] [nvarchar](15) NULL,
[FrameSize] smallint ) ;
BEGIN TRANSACTION ;
INSERT INTO Products ( [Name], [ProductNumber], [Colour] )
OUTPUT inserted.[ProductID], inserted.[Name], inserted.[ProductNumber], inserted.[Colour], inserted.[FrameSize] INTO Product_Audit( [ProductID], [Name], [ProductNumber], [Colour], [FrameSize] )
VALUES
( 'Mountain-500 Silver, 24', 'BK-M18S-24', 'Silver'),
( 'Mountain-500 Silver, 28', 'BK-M18S-28', 'Silver'),
( 'Mountain-500 Silver, 32', 'BK-M18S-32', 'Silver') ;
SELECT * FROM @Product_Audit ;
ROLLBACK TRANSACTION ;
INSERT INTO Product_Audit Product_Audit( [ProductID], [Name], [ProductNumber], [Colour], [FrameSize] )
SELECT Product_Audit [ProductID], [Name], [ProductNumber], [Colour], [FrameSize] FROM @Product_Audit
SELECT * FROM Product_Audit ;