Solutions to T-SQL Challenge - Duplicating Form Entries

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