From Stack Overflow:
I am trying to insert into the target table values from the source table, but also insert the primary key of the source table into the tracking table.INSERT INTO TRN_TEMP (TRN_TRAN_DATE, TRN_DESCRIPTION, TRN_AMT) OUTPUT D.DET_PRIMARY, GETDATE() INTO REGISTER (DET_PRIMARY_LINK, INS_DATE) SELECT D.DET_DATE, 'SOMETEXT', SUM(D.DET_NET) FROM DETAIL D LEFT JOIN REGISTER R ON R.DET_PRIMARY_LINK = D.DET_PRIMARY WHERE R.LINE_ID IS NULL -- TO REMOVE LINES ALREADY PROCESSED GROUP BY D.DET_DATE
I can’t see a problem with the text above but I get an error:The multi part identifier 'D.DET_PRIMARY' could not be bound.
I’ve tried both
DETAIL.DET_DETAILand the error is the same.
This is a limitation of SQL Server which can be easily worked around by substituting
MERGE instead of
Let’s create some sample tables and see:
CREATE TABLE source ( id INT NOT NULL PRIMARY KEY, grouper INT NOT NULL, value INT NOT NULL ) CREATE TABLE destination ( id INT NOT NULL IDENTITY PRIMARY KEY, grouper INT NOT NULL, aggregated_value INT NOT NULL ) CREATE TABLE register ( dest_id INT NOT NULL PRIMARY KEY, max_source_id INT NOT NULL UNIQUE, grouper INT NOT NULL UNIQUE, regdate DATE NOT NULL, ) INSERT INTO source VALUES (1, 1, 5), (2, 1, 2), (3, 2, 6), (4, 2, 8)
, and run the query:
MERGE INTO destination d USING ( SELECT grouper, SUM(value), MAX(id) FROM source WHERE grouper NOT IN ( SELECT grouper FROM register ) GROUP BY grouper ) s (grouper, aggregated_value, max_source_id) ON (1 = 0) WHEN NOT MATCHED THEN INSERT (grouper, aggregated_value) VALUES (grouper, aggregated_value) OUTPUT INSERTED.id, s.max_source_id, s.grouper, GETDATE() INTO register; SELECT * FROM register;
You can try it yourself on SQL Fiddle.
As you can see, we are using both destination table’s
INSERTED.id, source table’s
s.max_source_id, s.grouper and an arbitrary expression
GETDATE() in the
OUTPUT clause which works just alright.
An alert reader may notice that there is another problem with the original query, but this will be covered in the next post. Watch this space!