Using OUTPUT clause to insert value not in INSERTED

Question

I am writing a stored procedure to process a table belonging to an application and insert values into a table belonging to the same application (so I cannot amend either table).

I have to process only new records and remember which records have been processed, for this I have created a third simple table.

Tables are below, many columns removed to leave only important details.

Source Table

CREATE TABLE [dbo].[DETAIL](
    [DET_NET] [float] NULL,
    [DET_VAT] [float] NULL,
    [DET_VATCODE] [varchar](4) NULL,
    [DET_GROSS] [float] NULL,
    [DET_DATE] [datetime] NULL,
    [DET_PRIMARY] [float] NOT NULL
)

Target Table

CREATE TABLE [dbo].[TRN_TEMP](
    [TRN_TRAN_DATE] [datetime] NULL,
    [TRN_DESCRIPTION] [varchar](20) NULL,
    [TRN_PRIMARY] [int] NULL,
    [TRN_AMT] [float] NULL
)

Tracking Table

CREATE TABLE REGISTER(
    LINE_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,
    DET_PRIMARY_LINK FLOAT NOT NULL,
    INS_DATE DATETIME NOT NULL
)

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 D.DET_PRIMARY = R.DET_PRIMARY_LINK
WHERE <MY CONDITIONS> AND 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 D.DET_DETAIL and DETAIL.DET_DETAIL and the error is the same.

Is it not possible to use values from the source table in the OUTPUT clause when using a group or do I have an error in the formatting? If it's not possible is there another way of keeping track of which lines I have processed?

Quassnoi · Accepted Answer · 2013-03-25T14:24:17.580

11

Use MERGE instead of INSERT:

MERGE
INTO    trn_temp d
USING   (
        SELECT  D.DET_DATE, 'SOMETEXT' AS sometext, SUM(D.DET_NET) AS the_sum
        ...
        ) s
ON      (1 = 0)
WHEN NOT MATCHED THEN
INSERT  (TRN_TRAN_DATE, TRN_DESCRIPTION, TRN_AMT)
VALUES  (det_date, sometext, the_sum)
OUTPUT  s.*

Update:

To work around the GROUP BY problem, use this:

DECLARE @tmp TABLE
        (
        det_primary INT NOT NULL PRIMARY KEY
        )

MERGE
INTO    register r
USING   detail d
ON      (r.det_primary_link = d.det_primary)
WHEN NOT MATCHED THEN
INSERT  (det_primary_link, ins_date)
VALUES  (det_primary, GETDATE())
OUTPUT  d.det_primary
INTO    @tmp;

INSERT
INTO    trn_temp (trn_tran_date, trn_description, trn_amt)
OUTPUT  INSERTED.*
SELECT  det_date, 'sometext', SUM(det_net)
FROM    @tmp t
JOIN    detail d
ON      d.det_primary = t.det_primary
GROUP BY
        det_date

edited Mar 25 '13 at 14:24

answered Mar 25 '13 at 11:56

Quassnoi

413,100
91
616
614

This looks interesting, I don't quote understand 'what goes where' though, it looks like the target table goes after `MERGE INTO` but where do you declare the tracking table? Is this after the `OUTPUT` clause eg `OUTPUT s.* INTO [REGISTER]`? – bendataclear Mar 25 '13 at 12:36
1

@bendataclear: the whole thing above `OUTPUT` is equivalent to `INSERT INTO d SELECT * FROM (...) s`. Yes, you cant do `OUTPUT s.* INTO register` (or provide the comma delimited columns list). – Quassnoi Mar 25 '13 at 12:49
I think I have a problem, I am trying to output the `DET_PRIMARY` field but I am not grouping by this field, it's possible it may have 2 or more ID's included in each value. – bendataclear Mar 25 '13 at 12:56
I want BOTH ID's added to the tracking table as both rows have been processed. – bendataclear Mar 25 '13 at 13:04
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/26868/discussion-between-quassnoi-and-bendataclear) – Quassnoi Mar 25 '13 at 13:06
Seems to be the only working solution when we need to collect in the output table both ids from source (not to be inserted) and corresponding generated ids on the destination. Unfortunately, OP is about something different (just a syntax issue), but the answer is really useful! – d_f Oct 21 '22 at 15:47

score 1 · Answer 2 · answered Mar 05 '14 at 03:53

I know this question is nearly a year old, but your insert can work as you have presented it if you select the proper column name from the OUTPUT

The output clause gives us one of two possible virtual tables, inserted or deleted, depending on the operation, an update operation gives both. Since you don't have a field name DET_DATE in your TRN_TEMP table that you just inserted into, it is invalid in the output statement.

INSERT INTO TRN_TEMP (TRN_TRAN_DATE, TRN_DESCRIPTION, TRN_AMT)
OUTPUT INSERTED.TRN_TRAN_DATE, 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 D.DET_PRIMARY = R.DET_PRIMARY_LINK
WHERE <MY CONDITIONS> AND R.LINE_ID IS NULL -- TO REMOVE LINES ALREADY PROCESSED
GROUP BY D.DET_DATE

Although I've already implemented the `MERGE` answer I may look at changing this over as it looks like it will perform much better, thank you. — bendataclear, Mar 05 '14 at 13:24

Using OUTPUT clause to insert value not in INSERTED

2 Answers2

Linked

Related