sql - SQL 输出子句是否可以返回未插入的列？

Question

我对数据库进行了一些修改，需要将旧数据迁移到新表。为此，我需要填充一个表 ( ReportOptions )，从原始表 ( Practice ) 中获取数据，并填充第二个中间表 ( 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 中不属于目标表的字段条款，那就太好了(我想我不能，但我不确定)。关于如何满足我的需求有什么想法吗？

Answer 1

您可以使用 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 

<小时/>

更多阅读，我所知道的有关该主题的所有信息的来源是 here .