SQL Output子句是否可以返回未插入的列？(Is it possible to for SQL Output clause to return a column not being insert

SQL Output子句是否可以返回未插入的列？(Is it possible to for SQL Output clause to return a column not being inserted?)

我对我的数据库进行了一些修改，我需要将旧数据迁移到新表。为此，我需要填写一个表（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...)

我做了一个查询，以获取所有需要从练习到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子句上的目标表上的字段，那将是很好的（我想我不能，但我不知道肯定）。关于如何满足我的需求的任何想法？

提前致谢。

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...)

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

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 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);

关键是在merge语句中使用永远不会为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

更多的阅读，以及我所知道的所有这一切的来源在这里

You can do this by using MERGE instead of insert:

so replace this

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

with

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);

The key is to use a statement that will never be true (1 = 0) in the merge statement, 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

More reading, and the source of all that I know on the subject is Here

更多推荐