SQL Server 2016 – Using OUTPUT Inserted

sql serversql-server-2016

Is it possible to apply a condition on an output.inserted statement?

DECLARE @tblA TABLE (foo INT)
DECLARE @tblOut TABLE (bar INT)

INSERT INTO @tblA 
OUTPUT inserted.foo INTO @tblOut(bar) --WHERE foo = 1
VALUES (1),(2),(3)

Best Answer

As far as I know, it is not possible to apply condition on OUTPUT directly, but you can achieve what you need using output of the MERGE:

INSERT INTO @tblOut(bar)
SELECT foo
FROM (
    MERGE INTO @tblA t
    USING (VALUES (1),(2),(3)) s(foo)
    ON 1 = 0
    WHEN NOT MATCHED THEN
        INSERT (foo)
        VALUES (s.foo)
    OUTPUT inserted.foo
) m
WHERE m.foo = 1;
Related Question