Est-il possible de faire en sorte que la clause SQL Output renvoie une colonne qui n'a pas été insérée ?

Question

J'ai apporté quelques modifications à ma base de données et je dois migrer les anciennes données vers les nouvelles tables. Pour cela, je dois remplir une table ( ReportOptions ) en prenant les données du tableau original ( Practice ) et remplissent un deuxième tableau intermédiaire ( 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...
)

J'ai créé une requête pour obtenir toutes les données dont j'ai besoin pour passer de Practice a ReportOptions mais je n'arrive pas à remplir le tableau intermédiaire.

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

Si je pouvais référencer un champ qui ne fait pas partie de la table de destination dans le fichier OUTPUT Ce serait formidable (je pense que je ne peux pas, mais je n'en suis pas sûr). Comment faire pour répondre à mon besoin ?

Answer 1

Vous pouvez le faire en utilisant MERGE au lieu de INSERT .

Remplacez donc ceci :

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

avec :

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

La clé est d'utiliser un prédicat qui ne sera jamais vrai ( 1 = 0 ) dans la condition de recherche de fusion, de sorte que vous effectuerez toujours l'insertion, mais que vous aurez accès aux champs des tables source et destination.

---

Voici le code complet que j'ai utilisé pour le tester :

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 

---

Plus de lecture, et la source de tout ce que je sais sur le sujet est aquí .

Answer 2

Peut-être quelqu'un qui utilise MS SQL Server 2005 ou version inférieure trouveront cette réponse utile.

---

MERGE ne fonctionnera que pour SQL Server 2008 ou supérieur.

Pour le reste, j'ai trouvé une autre solution qui vous permettra de créer des tables de correspondance.

Voici à quoi ressemblera la résolution pour 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

L'astuce principale consiste à remplir deux fois la table de correspondance avec des données ordonnées provenant de la table source et de la table de destination.

Pour plus de détails, voir Fusionner des données insérées en utilisant OUTPUT dans SQL Server 2005 .