SQLOutput 子句是否可能返回未插入的列？

我已经对数据库做了一些修改，需要将旧数据迁移到新表中。为此，我需要从原始表(Practice)获取数据填充一个表(ReportOptions) ，并填充第二个中间表(PracticeReportOption)。

ReportOption (
ReportOptionId int PK,
field1, field2...
)
Practice (
PracticeId int PK,
field1, field2...
)
PracticeReportOption (
PracticeReportOptionId int PK,
PracticeId int FK,
ReportOptionId int FK,
field1, field2...
)

我进行了一个查询，以获得从 Practice移动到 ReportOptions所需的所有数据，但是在填充中间表时遇到了麻烦。

--Auxiliary tables
DECLARE @ReportOption TABLE (
PracticeId int, -- This field is not on the actual ReportOption table
field1, field2...
)
DECLARE @PracticeReportOption TABLE (
PracticeId int,
ReportOptionId int,
field1, field2
)


--First I get all the data I need to move
INSERT INTO @ReportOption
SELECT P.practiceId, field1, field2...
FROM Practice P


--I insert it into the new table,
--but somehow I need to have the repation PracticeId / ReportOptionId
INSERT INTO ReportOption (field1, field2...)
OUTPUT @ReportOption.PracticeId, --> this is the field I don't know how to get
inserted.ReportOptionId
INTO @PracticeReportOption (PracticeId, ReportOptionId)
SELECT field1, field2
FROM @ReportOption


-- This would insert the relationship, If I knew how to get it!
INSERT INTO @PracticeReportOption (PracticeId, ReportOptionId)
SELECT PracticeId, ReportOptionId
FROM @ReportOption

如果我能在 OUTPUT子句中引用一个不来自目标表的字段，那就太好了(我想我不能，但我不确定)。有什么办法可以满足我的需求吗？

55003

小开

最佳答案

您可以通过使用 MERGE而不是 INSERT来实现这一点。

所以换个说法:

INSERT INTO ReportOption (field1, field2...)
OUTPUT @ReportOption.PracticeId, --> this is the field I don't know how to get
inserted.ReportOptionId
INTO @PracticeReportOption (PracticeId, ReportOptionId)
SELECT field1, field2
FROM @ReportOption

与:

MERGE INTO ReportOption USING @ReportOption AS temp ON 1 = 0
WHEN NOT MATCHED THEN
INSERT (field1, field2)
VALUES (temp.Field1, temp.Field2)
OUTPUT temp.PracticeId, inserted.ReportOptionId, inserted.Field1, inserted.Field2
INTO @PracticeReportOption (PracticeId, ReportOptionId, Field1, Field2);

关键是使用一个在合并搜索条件下永远不会为 true 的谓词(1 = 0) ，因此您将始终执行插入操作，但是可以访问源表和目标表中的字段。

下面是我用来测试它的全部代码:

CREATE TABLE ReportOption (
ReportOptionID INT IDENTITY(1, 1),
Field1 INT,
Field2 INT
)
CREATE TABLE Practice (
PracticeID INT IDENTITY(1, 1),
Field1 INT,
Field2 INT
)
CREATE TABLE PracticeReportOption (
PracticeReportOptionID INT IDENTITY(1, 1),
PracticeID INT,
ReportOptionID INT,
Field1 INT,
Field2 INT
)


INSERT INTO Practice VALUES (1, 1), (2, 2), (3, 3), (4, 4)




MERGE INTO ReportOption r USING Practice p ON 1 = 0
WHEN NOT MATCHED THEN
INSERT (field1, field2)
VALUES (p.Field1, p.Field2)
OUTPUT p.PracticeId, inserted.ReportOptionId, inserted.Field1, inserted.Field2
INTO PracticeReportOption (PracticeId, ReportOptionId, Field1, Field2);


SELECT *
FROM PracticeReportOption


DROP TABLE ReportOption
DROP TABLE Practice
DROP TABLE PracticeReportOption

更多的阅读，我所知道的关于这个主题的来源是给你。

DECLARE @ReportOption TABLE ( ReportOptionID INT IDENTITY(1, 1), Field1 INT, Field2 INT ) DECLARE @Practice TABLE( PracticeID INT IDENTITY(1, 1), Field1 INT, Field2 INT ) DECLARE @PracticeReportOption TABLE( PracticeReportOptionID INT IDENTITY(1, 1), PracticeID INT, ReportOptionID INT, Field1 INT, Field2 INT ) INSERT INTO @Practice (Field1, Field2) VALUES (1, 1) INSERT INTO @Practice (Field1, Field2) VALUES (2, 2) INSERT INTO @Practice (Field1, Field2) VALUES (3, 3) INSERT INTO @Practice (Field1, Field2) VALUES (4, 4) INSERT INTO @ReportOption (field1, field2) OUTPUT INSERTED.ReportOptionID, INSERTED.Field1, INSERTED.Field2 INTO @PracticeReportOption (ReportOptionID, Field1, Field2) SELECT Field1, Field2 FROM @Practice ORDER BY PracticeID ASC; WITH CTE AS ( SELECT PracticeID, ROW_NUMBER() OVER ( ORDER BY PracticeID ASC ) AS ROW FROM @Practice ) UPDATE M SET M.PracticeID = S.PracticeID FROM @PracticeReportOption AS M JOIN CTE AS S ON S.ROW = M.PracticeReportOptionID SELECT * FROM @PracticeReportOption