CODEWITHSUNDEEP
sqlsql-servert-sql
TomFp
TomFp

Reputation: 567

T-SQL output inserted clause - access data not in the inserted/deleted tables

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 am using Microsoft SQL Server 2017

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

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
*/

The OUTPUT clause cannot access anything not in the INSERTED or DELETED internal tables - which is the cause of the error. However this example Microsoft T-SQL OUTPUT CLAUSE (albeit about DELETED) seems to suggest you can access other tables.

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

Upvotes: 3

Views: 1430

Answers (1)

TomFp
TomFp

Reputation: 567

Using the MERGE statement - as Suggested by Tab Alleman here is the solution:

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

Upvotes: 2

Related Questions