OUTPUT

OUTPUT klauzule přišla do SQL Serveru již v edici 2008, ale stále je to výrazně nedoceněná funkcionalita a spousta vývojářů raději nejprve vloží data do tabulky a pak se teprve pomocí WHERE snaží přesvědčit, že se vše povedlo. Proto jsem pro Vás připravil krátký přehled všech vychytávek, které s OUTPUT klauzulí můžeme dělat pro základní DML operace.

Zopakujme si ale nejprve základní fakta o použití OUTPUT:

• vrací data pro každý řádek, který byl ovlivněn některým z příkazů INSERT, UPDATE, DELETE nebo MERGE (DML operace)
• výstup je možné vrátit rovnou jako data klientovi nebo uložit do tabulky, temporární tabulky nebo table variable
• OUTPUT je možné použít i jako vnořenou část všech čtyř DML příkazů
• s výstupními daty lze přímo pracovat pomocí nejrůznějších výrazů

Příklady jsou zpracovány co nejjednodušší formou pro rychlé použíti metodou copy & paste.

INSERT:

CREATE TABLE dbo.SampleTable
(Id INT IDENTITY(1,1) PRIMARY KEY,
 Name VARCHAR(50)
)
GO

-- OUTPUT from INSERT into @TableVariable

DECLARE @InsertedRows TABLE (RowId INT)

INSERT INTO dbo.SampleTable
    OUTPUT INSERTED.Id
        INTO @InsertedRows
VALUES
    ('Name1'), 
    ('Name2'),
    ('Name3'),
    ('Name4'),
    ('Name5')

SELECT * FROM @InsertedRows
GO


-- OUTPUT from INSERT into ##TemporaryTable

CREATE TABLE ##tmp (RowId INT)

INSERT INTO dbo.SampleTable
    OUTPUT INSERTED.Id
        INTO ##tmp
VALUES
    ('Name1'), 
    ('Name2'),
    ('Name3'),
    ('Name4'),
    ('Name5')

SELECT * FROM ##tmp
GO

-- OUTPUT from INSERT into UserTable

CREATE TABLE UserTable (RowId INT)
GO

INSERT INTO dbo.SampleTable
    OUTPUT INSERTED.Id
        INTO UserTable
VALUES
    ('Name1'), 
    ('Name2'),
    ('Name3'),
    ('Name4'),
    ('Name5')

SELECT * FROM UserTable
GO

-- OUTPUT from INSERT with *

DECLARE @InsertedRows TABLE (RowId INT, Name VARCHAR(50))

INSERT INTO dbo.SampleTable
    OUTPUT INSERTED.*
        INTO @InsertedRows
VALUES
    ('Name1'), 
    ('Name2'),
    ('Name3'),
    ('Name4'),
    ('Name5')

SELECT * FROM @InsertedRows
GO

-- OUTPUT from multiple INSERTs

DECLARE @InsertedRows TABLE (RowId INT, Name VARCHAR(50))

INSERT INTO dbo.SampleTable
    OUTPUT INSERTED.*
        INTO @InsertedRows
VALUES
    ('Name1')

INSERT INTO dbo.SampleTable
    OUTPUT INSERTED.*
        INTO @InsertedRows
VALUES
    ('Name2')

SELECT * FROM @InsertedRows
GO

-- OUTPUT from INSERT from SELECT

DECLARE @InsertedRows TABLE (RowId INT, Name VARCHAR(50))

INSERT INTO dbo.SampleTable
    OUTPUT INSERTED.*
        INTO @InsertedRows
SELECT 'Name1' 

SELECT * FROM @InsertedRows
GO

-- OUTPUT from INSERT with column names on output table

DECLARE @InsertedRows TABLE (RowId INT, Name VARCHAR(50))

INSERT INTO dbo.SampleTable
    OUTPUT INSERTED.Id
        INTO @InsertedRows (RowId)
SELECT 'Name1' 

SELECT * FROM @InsertedRows
GO

-- OUTPUT from INSERT with SELECT output values only
INSERT INTO dbo.SampleTable
    OUTPUT INSERTED.*
VALUES
    ('Name1'), 
    ('Name2'),
    ('Name3'),
    ('Name4'),
    ('Name5')

UPDATE:

CREATE TABLE dbo.SampleTable
(Id INT IDENTITY(1,1) PRIMARY KEY,
 Name VARCHAR(50)
)
GO

INSERT INTO dbo.SampleTable
VALUES
    ('Name1'), 
    ('Name2'),
    ('Name3'),
    ('Name4'),
    ('Name5')

SELECT * FROM dbo.SampleTable
GO

-- OUTPUT from UPDATE with SELECT output values only
UPDATE dbo.SampleTable
    SET Name = 'Name3UPDATED'
OUTPUT 
    INSERTED.Id,
    DELETED.Id,
    INSERTED.Name,
    DELETED.Name
WHERE Id = 3
GO

-- OUTPUT from UPDATE into @TableVariable
DECLARE @Updated TABLE (Id INT,
                        NewValue VARCHAR(50),
                        OldValue VARCHAR(50))

UPDATE dbo.SampleTable
    SET Name = 'Name4UPDATED'
OUTPUT 
    INSERTED.Id,
    INSERTED.Name,
    DELETED.Name
    INTO @Updated
WHERE Id = 4

SELECT * FROM @Updated
GO

DELETE:

CREATE TABLE dbo.SampleTable
(Id INT IDENTITY(1,1) PRIMARY KEY,
 Name VARCHAR(50)
)
GO

INSERT INTO dbo.SampleTable
VALUES
    ('Name1'), 
    ('Name2'),
    ('Name3'),
    ('Name4'),
    ('Name5')
GO

-- OUTPUT from DELETE with SELECT output values only
DELETE FROM dbo.SampleTable
    OUTPUT DELETED.Id,
           DELETED.Name
WHERE Id = 3
GO

-- OUTPUT from DELETE into @TableVariable
DECLARE @Deleted TABLE (Id INT)

DELETE FROM dbo.SampleTable
    OUTPUT DELETED.Id
        INTO @Deleted
WHERE Id = 5

SELECT * FROM @Deleted
GO