Last week I provided a T-SQL challenge involving duplicating form entries. You can find details about the challenge here. I’d like to thank all those who participated and sent solutions: Steve Kass, Peter Larsson (Peso), Umachandar Jayachandran (UC), Will Alber, Casimor Casimor, Calvin Westervelt, and Michael DeFehr.
The solution to the first part of the puzzle isn’t too difficult, and most people got it right. You need to use one statement to perform two insertions—one to the table Fields and another to FieldMappings. The tricky part is that you somehow need to relate the old form fields (queried rows) with the new ones (inserted rows), which are generated by the IDENTITY property. Obviously you need to use the OUTPUT clause; but using an INSERT statement, you don’t have a way to relate queried and inserted rows. The trick is to use the MERGE statement since in the OUTPUT clause this statement allows you to refer to both source and inserted attributes from the related rows. Since the only action that you need to apply is an INSERT in any case, you can use a false predicate in the ON clause (e.g., 1 = 2), and apply the INSERT action in the WHEN NOT MATCHED clause. Fortunately, the MERGE statement doesn’t require more than one WHEN clause to be present.
As for the solution itself, there are two variations. One uses OUTPUT INTO, like so:
-- Using OUTPUT INTO
DECLARE @source_formid AS INT = 1;
INSERT INTO dbo.Forms(name) VALUES('NewForm');
DECLARE @target_formid AS INT = SCOPE_IDENTITY();
MERGE INTO dbo.Fields AS TGT
USING (SELECT id, name, value
FROM dbo.Fields
WHERE formid = @source_formid) AS SRC
ON 1 = 2
WHEN NOT MATCHED THEN
INSERT (formid, name, value)
VALUES (@target_formid, src.name, src.value)
OUTPUT SRC.id AS theoldid, inserted.id AS thenewid
INTO dbo.FieldMappings(theoldid, thenewid);
Another uses composable DML:
DECLARE @source_formid AS INT = 1;
INSERT INTO dbo.Forms(name) VALUES('NewForm');
DECLARE @target_formid AS INT = SCOPE_IDENTITY();
INSERT INTO dbo.FieldMappings(theoldid, thenewid)
SELECT theoldid, thenewid
FROM (MERGE INTO dbo.Fields AS TGT
USING (SELECT id, name, value
FROM dbo.Fields
WHERE formid = @source_formid) AS SRC
ON 1 = 2
WHEN NOT MATCHED THEN
INSERT (formid, name, value)
VALUES (@target_formid, src.name