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, src.value)
OUTPUT SRC.id AS theoldid, inserted.id AS thenewid) AS D;
As for part 2 of the puzzle… the problem with the aforementioned solutions is that neither is supported when the target table participates in any side of a primary key-foreign key relationship. After creating the foreign keys, if you run the solution that uses OUTPUT INTO, you get the following error.
Msg 332, Level 16, State 1, Line 5
The target table 'dbo.FieldMappings' of the OUTPUT INTO clause cannot be on either side of a (primary key, foreign key) relationship. Found reference constraint 'FK_FieldMappings_Fileds_1'.
If you run the solution that uses composable DML, you get the following error:
Msg 356, Level 16, State 1, Line 5
The target table 'FieldMappings' of the INSERT statement cannot be on either side of a (primary key, foreign key) relationship when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement. Found reference constraint 'FK_FieldMappings_Fileds_1'.
Personally I wasn’t sure that a solution exists to part 2. But Steve Kass (and later Will Alber with the help of a hint from Steve) managed to come up with a solution. Apparently, when inserting the output of a dynamic batch into the target, and the dynamic batch has a modification statement with the OUTPUT clause, the primary key-foreign key restrictions do not apply. Here’s the solution:
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)
EXEC sp_executesql
@stmt = N'
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;',
@params = N'@source_formid INT, @target_formid INT',
@source_formid = @source_formid,
@target_formid = @target_formid;
When you see the solution, you feel it’s so simple. But that’s the beauty of it!
Cheers,
BG