使用 OUTPUT 子句插入不在 INSERTED 中的值 [英] Using OUTPUT clause to insert value not in INSERTED

问题描述

我正在编写一个存储过程来处理属于某个应用程序的表并将值插入到属于同一应用程序的表中(因此我无法修改任一表).

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.

源表

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
)

目标表

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

跟踪表

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

我看不出上面的文字有什么问题，但我收到一条错误消息无法绑定多部分标识符‘D.DET_PRIMARY’.".我尝试了 D.DET_DETAIL 和 DETAIL.DET_DETAIL 并且错误是相同的.

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.

在使用组时，是否无法在 OUTPUT 子句中使用源表中的值，或者我的格式有错误?如果不可能，还有其他方法可以跟踪我处理过的行吗?

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?

推荐答案

使用MERGE代替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.*

更新:

要解决 GROUP BY 问题，请使用:

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

这篇关于使用 OUTPUT 子句插入不在 INSERTED 中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！