SQL Output 절이 삽입되지 않은 열을 반환 할 수 있습니까?

SQL Output 절이 삽입되지 않은 열을 반환 할 수 있습니까?

---

데이터베이스를 일부 수정했으며 이전 데이터를 새 테이블로 마이그레이션해야합니다. 이를 위해 원본 테이블 (실습)에서 데이터를 가져 오는 테이블 (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 절의 대상 테이블에없는 필드를 참조 할 수 있다면 훌륭 할 것입니다 (확실하지는 않지만 확실하지는 않습니다). 내 필요를 달성하는 방법에 대한 아이디어가 있습니까?

---

MERGEinsert 대신 에 사용하면 됩니다 :

그래서 이것을 교체하십시오

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

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

---

Maybe someone who uses MS SQL Server 2005 or lower will find this answer useful.

---

MERGE will work only for SQL Server 2008 or higher. For rest I found another workaround which will give you ability to create kind of mapping tables.

Here's how Resolution will look like for SQL 2005:

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

Main trick is that we are filling mapping table twice with ordered data from Source and destination table. For More details here: Merging Inserted Data Using OUTPUT in SQL Server 2005

참고URL : https://stackoverflow.com/questions/10949730/is-it-possible-to-for-sql-output-clause-to-return-a-column-not-being-inserted