SQL 输出子句是否可以返回未插入的列? [英] Is it possible to for SQL Output clause to return a column not being inserted?

问题描述

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

I've made some modifications to my database and I need to migrate the old data to the new tables. For that, I need to fill a table (ReportOptions) taking the data from the original table (Practice), and fill a second intermediate table (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...)

我进行了查询以获取从实践移动到 ReportOptions 所需的所有数据，但我无法填充中间表

I made a query to get all the data I need to move from Practice to ReportOptions, but I'm having trouble to fill the intermediate table

--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 子句中引用不在目标表上的字段，那就太好了(我想我不能，但我不确定).关于如何满足我的需求的任何想法?

If I could reference a field that is not on the destination table on the OUTPUT clause, that would be great (I think I can't, but I don't know for sure). Any ideas on how to accomplish my need?

推荐答案

您可以使用 MERGE 而不是 insert 来做到这一点:

You can do this by using MERGE instead of 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);

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

The key is to use a predicate that will never be true (1 = 0) in the merge search condition, so you will always perform the insert, but have access to fields in both the source and destination tables.

这是我用来测试它的完整代码:

Here is the entire code I used to test it:

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 

<小时>

更多阅读，以及我所知道的关于这个主题的所有信息的来源是 这里

这篇关于SQL 输出子句是否可以返回未插入的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！