Returns
information from, or expressions based on, each row affected by an INSERT,
UPDATE, DELETE, or MERGE statement. These results can be returned to the
processing application for use in such things as confirmation messages,
archiving, and other such application requirements. The results can also be
inserted into a table or table variable. Additionally, you can capture the
results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE
statement, and insert those results into a target table or view.
OUTPUT
clause can generate table variable, a permanent table, or temporary table.
Using an OUTPUT Clause in an INSERT Statement
When
you insert data into a table, you can use the OUTPUT clause to return a copy of
the data that’s been inserted into the table. The OUTPUT clause takes two basic
forms: OUTPUT and OUTPUT INTO. Use the OUTPUT form if you want to return the
data to the calling application. Use the OUTPUT INTO form if you want to return
the data to a table or a table variable.
Create Table MyTable (ID int, name varchar(100),createdAt datetime)
-- declare @InsertOutput2 table variable
DECLARE @InsertOutput2 table
(
ID int,
name nvarchar(50),
CreatedAt datetime
);
-- insert new row into table in your database
INSERT INTO MyTable
OUTPUT
INSERTED.ID,
INSERTED.Name,
INSERTED.CreatedAt
INTO @InsertOutput2
VALUES(111, 'Some text', GETDATE());
-- view inserted row in table
SELECT * FROM MyTable;
-- view output row in @InsertOutput2 variable
SELECT * FROM @InsertOutput2;
drop table MyTable
Using an OUTPUT Clause in an UPDATE Statement
Create Table MyTable (ID int, name varchar(100),createdAt datetime)
-- declare @InsertOutput2 table variable
DECLARE @UpdateOutput1 table
(
ID int,
name nvarchar(50),
Oldname nvarchar(50),
CreatedAt datetime,
UpdatedAt datetime
);
-- insert new row into table in your database
INSERT INTO MyTable
VALUES(111, 'Some text', GETDATE());
UPDATE MyTable
SET
name = 'Changed...'
OUTPUT
INSERTED.ID,
INSERTED.Name,
DELETED.Name,
DELETED.CreatedAt,
getdate()
INTO @UpdateOutput1
WHERE Name = 'Some text';
-- view inserted row in table
SELECT * FROM MyTable;
-- view output row in @InsertOutput2 variable
SELECT * FROM @UpdateOutput1;
drop table MyTable
In
the same manner you can use it with DELETE statement. It uises the same
INSERTED and DELETED memory tables.
Using an OUTPUT Clause in an MERGE Statement
When
working in SQL Server 2008, you can add an OUTPUT clause to a MERGE statement.
The process is similar to adding the clause to an UPDATE statement; you use
both the INSERTED and DELETED column prefixes.
Let’s
look at an example that demonstrates how this work. First, however, we must
create a second table to support the MERGE statement. The following script
creates the Book2 table and populates it with two rows:
CREATE TABLE dbo.Books2
(
BookID int NOT NULL PRIMARY KEY,
BookTitle nvarchar(50) NOT NULL,
ModifiedDate datetime NOT NULL
);
CREATE TABLE dbo.Books
(
BookID int NOT NULL PRIMARY KEY,
BookTitle nvarchar(50) NOT NULL,
ModifiedDate datetime NOT NULL
);
INSERT INTO Books2
VALUES(101, '100 Years of Solitude', GETDATE());
INSERT INTO Books2
VALUES(102, 'Pride & Prejudice', GETDATE());
--Once we’ve created the Books2 table, we can try a MERGE statement. In the following example, I declare the @MergeOutput1 variable, merge data from the Books table into the Books2 table, and view the results:
-- declare @MergeOutput1 table variable
DECLARE @MergeOutput1 table
(
ActionType nvarchar(10),
BookID int,
OldBookTitle nvarchar(50),
NewBookTitle nvarchar(50),
ModifiedDate datetime
);
INSERT INTO Books
VALUES(101, 'The Great Gatsby', GETDATE());
-- use MERGE statement to perform update on Book2
MERGE Books2 AS b2
USING Books AS b1
ON (b2.BookID = b1.BookID)
WHEN MATCHED
THEN UPDATE
SET b2.BookTitle = b1.BookTitle
OUTPUT
$action,
INSERTED.BookID,
DELETED.BookTitle,
INSERTED.BookTitle,
INSERTED.ModifiedDate
INTO @MergeOutput1;
-- view Books table
SELECT * FROM Books;
-- view updated rows in Books2 table
SELECT * FROM Books2;
-- view output rows in @MergeOutput1 variable
SELECT * FROM @MergeOutput1;
drop table Books
drop table Books2
$action
Gives us the type of action that is performed by Merge statement per record.
