Output Clause Explained

Please explain to me t开发者_StackOverflow社区he SQL Server output clause.

Do some examples help? The below all output the result to the client but you can also OUTPUT INTO a @table_variable (or a standard table under certain conditions)

create table T
(
id int identity(1,1),
c char(1)
)

insert into T(c) 
OUTPUT inserted.* /*Output Inserted Rows - shows the ids that were allocated*/
values ('A'),('B'),('C')

Returns

id          c
----------- ----
1           A
2           B
3           C

.

UPDATE T
SET c = CHAR(ASCII(c)+1)
 /*Output before and after versions of each row*/
OUTPUT deleted.*, inserted.*
WHERE id IN (2,3)

Returns

id          c    id          c
----------- ---- ----------- ----
2           B    2           C
3           C    3           D

.

DELETE 
FROM T
 /*Output the row(s) that were deleted*/
OUTPUT deleted.*
WHERE DATEPART(second, getdate())%id = 0

Returns (for example)

id          c
----------- ----
1           A

Edit:

In response to comment some examples showing how OUTPUT can be used to insert to a table.

CREATE TABLE #T2
(
id UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID(),
c CHAR(1)
)

DECLARE @inserted TABLE
(
id UNIQUEIDENTIFIER,
c CHAR(1)
)

INSERT INTO #T2(c) 
OUTPUT inserted.id, inserted.c 
INTO @inserted
VALUES ('A')

If you are on SQL Server 2008 you can use composable DML

INSERT INTO @inserted
SELECT I.id, I.c
FROM 
    (
    INSERT INTO #T2(c) 
    OUTPUT inserted.id, inserted.c 
    VALUES ('B'),('C')
    ) AS I
WHERE c <> 'C' --Only add rows of interest to @inserted table

But you will get an error message if the insert target participates in a PK/FK relationship. If you encounter this problem you can also use this pattern.

INSERT INTO @inserted
EXEC sp_executesql N'
    INSERT INTO #T2(c) 
    OUTPUT inserted.id, inserted.c 
    VALUES (''D''),(''E'') '

It could be stated as

How do I find out what rows were deleted, inserted or updated?

You can using some fancy trigger code or a separate SELECT, but the OUTPUT clause make it effortless. The SELECT becomes part of the actual DELETE, INSERT or UPDATE

The OUTPUT clause allows you to combine an INSERT or UPDATE with a SELECT.

You can OUTPUT a list of fields, and the query will return one row for each row affected by the INSERT / UPDATE.