Home

Transact-SQL Keywords: OUTPUT

  

Description

After performing data entry on a table or a view, to output the results in a separate table, you can use the OUTPUT operator. The formula to follow is:

INSERT INTO TableName
OUTPUT INSERTED.Columns
VALUES(Value_1, Value_2, Value_X)

Here is an example:

USE VideoCollection;
GO

CREATE TABLE Videos
(
	Title nvarchar(50), 
	Director nvarchar(50),
	WideScreen bit,
	Rating nchar(10),
	YearReleased int
)
GO

INSERT INTO Videos
OUTPUT inserted.*
VALUES(N'War of the Roses (The)', N'Dany de Vito', 0, N'R', 2001),
      (N'Memoirs of a Geisha', N'Rob Marshall', 1, N'PG-13', 2006),
      (N'Last Castle (The)', N'Rod Lurie', 1, N'', 2001),
      (N'Sneakers', N'Phil Alden Robinson', 1, N'PG-13', 2003);
GO

When this statement executes, if you are working in the Microsoft SQL Server Management Studio, the lower part would display a list of the records that were added:

OUTPUT

If you want to store the list of newly created records in a table, on the right side of the INSERTED operator and its period, type INTO followed by the name of the table that will receive the values. Here is an example:

USE VideoCollection;
GO

CREATE TABLE Archives
(
	Title nvarchar(50), 
	Director nvarchar(50),
	WideScreen bit,
	Rating nchar(10),
	YearReleased int
)
GO

INSERT INTO Videos
OUTPUT inserted.* INTO Archives
VALUES(N'Two for the Money', N'D.J. Caruso', 1, N'R', 2006),
      (N'Wall Street', N'Oliver Stone', 0, N'R', 2000);
GO

If you want to get the list of newly inserted records, after the OUTPUT keyword, type INSERTED followed by a period and followed by the name of the first column. The formula to use is:

INSERT INTO TableName(Column_1, Column_2, Column_X)
OUTPUT INSERTED.Column_1, INSERTED.Column_2, INSERTED.Column_X
VALUES(Value_1, Value_2, Value_X)

You can list the columns in an order of your choice, as long as both the TableName and the OUTPUT section use the exact same order. Here is an example:

USE VideoCollection;
GO

INSERT INTO Videos(Director, Rating, Title)
OUTPUT inserted.Director, inserted.Rating, inserted.Title
VALUES(N'Jonathan Lynn', N'R', N'Distinguished Gentleman (The)'),
      (N'Paul Anderson', N'R', N'Soldier');
GO

When the statement has executed, the result would display in the lower portion of the Microsoft SQL Server Management Studio. If you want to store the result in a table, use the following formula

INSERT INTO TableName(Column_1, Column_2, Column_X)
OUTPUT INSERTED.Column_1, INSERTED.Column_2, INSERTED.Column_X INTO TargetTable
VALUES(Value_1, Value_2, Value_X)

Here is an example:

USE VideoCollection;
GO

CREATE TABLE Entertainment
(
	Title nvarchar(50), 
	Director nvarchar(50)
)
GO

INSERT INTO Videos(Title, Director)
OUTPUT inserted.Title, inserted.Director INTO Entertainment
VALUES(N'Michael Jackson Live in Bucharest', N'Andy Morahan'),
      (N'Outfoxed', N'Robert Greenwald');
GO
 

Home Copyright © 2009-2011 FunctionX.com