Help with OUTPUT INSERTED syntax needed

  • I want to collect a value from the source table of a SELECT statement used in an INSERT statement, that is NOT inserted into the target table

    I think the following code explains what I'm trying to do:  Just cut and paste into SSMS 

    DECLARE @CrossRef TABLE (
        MyTable_ID INT,
        C_C VARCHAR(10)
        );
    DECLARE @MyData TABLE (
        A VARCHAR(10),
        B VARCHAR(10),
        C VARCHAR(10) );
    INSERT INTO @MyData (A, B, C)
    VALUES ('A1', 'B1', 'C1'), ('A2', 'B2', 'C2'),('A3', 'B3', 'C3');

    DECLARE @MyTable TABLE (
        ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
        A VARCHAR(10),
        B VARCHAR(10) );

    INSERT INTO @MyTable (A, B)
    OUTPUT INSERTED.Id, MyData.C
    INTO @CrossRef (MyTable_ID, C_C)
    SELECT A, B
    FROM @MyData AS MyData

    -- Error: The multi-part identifier "MyData.C" could not be bound.

    -- DESIRED OUTPUT
    SELECT * FROM @MyTable
    /*
    ID    A    B
    ----------
    1    A1    B1
    2    A2    B2
    3    A3    B3
    */
    SELECT * FROM @CrossRef
    /*
    MyTable_ID C_C
    ---------------
    1            C1
    2            C2
    3            C3
    */


    Note - The example has been highly simplified to make the issue as clear as possible
    It may seem trivial to get the desired output by other means, but like anything in production the real situation is much more complex

  • The problem is you are trying to OUTPUT a column from table @MyTable that does not exist on @MyTable(MyData.C).

  • The help on the OUTPUT clause seems to suggest that data not deleted can be accessed in the DELETED table 

    https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-2017#j-using-output-and-output-into-in-a-single-statement

    I want to find out if something similar is possible for INSERTED

  • I've figured it out - using the MERGE statement instead of INSERT


    DECLARE @CrossRef TABLE (
        MyTable_ID INT,
        C_C VARCHAR(10)
        );
    DECLARE @MyData TABLE (    
        ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
        A VARCHAR(10),
        B VARCHAR(10),
        C VARCHAR(10) );
    INSERT INTO @MyData (A, B, C)
    VALUES ('A1', 'B1', 'C1'), ('A2', 'B2', 'C2'),('A3', 'B3', 'C3');

    DECLARE @MyTable TABLE (
        ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
        A VARCHAR(10),
        B VARCHAR(10) );

    -- MERGE statement does UPDATE where join condition exists and INSERT where it does not
    MERGE @MyTable
    USING (SELECT A, B, C FROM @MyData) AS [Source]
    ON (1=0) -- join never true so everything inserted, nothing updated
    WHEN NOT MATCHED THEN
        INSERT (A, B)
        VALUES ([Source].A, [Source].B)
        OUTPUT INSERTED.Id, [Source].C
        INTO @CrossRef (MyTable_ID, C_C);

    SELECT * FROM @MyData
    SELECT * FROM @MyTable
    SELECT * FROM @CrossRef

  • tom 69406 - Monday, March 4, 2019 9:29 AM

    The help on the OUTPUT clause seems to suggest that data not deleted can be accessed in the DELETED table 

    https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-2017#j-using-output-and-output-into-in-a-single-statement

    I want to find out if something similar is possible for INSERTED

    Yes, you can access anything with inserted/deleted that's in the table in question. But MyData.C is in a different table.
    deleted is the value before the statement execution (if it's an insert then it's null)
    inserted is the value after the statement execution (if it's a delete then it's null)

  • The basic problem here is that you were trying to use a column that was not participating in the INSERT.  Can't do that without using MERGE, and that does appear to be a creative use of it to expose that additional data.   Very interesting, and wondering about whether or not there might be performance implications at high data volumes.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply