T-SQL Challenge - Duplicating Form Entries

This is a nice little T-SQL challenge that I got from my friend Davide Mauri. You are given the following tables and sample data:

SET NOCOUNT ON;

USE tempdb;

GO

IF OBJECT_id('dbo.FieldMappings', 'U') IS NOT NULL

  DROP TABLE dbo.FieldMappings;

IF OBJECT_id('dbo.Fields'       , 'U') IS NOT NULL

  DROP TABLE dbo.Fields;

IF OBJECT_id('dbo.Forms'        , 'U') IS NOT NULL

  DROP TABLE dbo.Forms;

GO

CREATE TABLE dbo.Forms

(

  id   INT         NOT NULL IDENTITY(1,1),

  name VARCHAR(32) NOT NULL,

  CONSTRAINT PK_Forms PRIMARY KEY(id)

);

CREATE TABLE dbo.Fields

(

  id     INT          NOT NULL IDENTITY(1,1),

  formid INT          NOT NULL, -- related to dbo.Forms(id)

  name   VARCHAR(32)  NOT NULL,

  value  VARCHAR(256) NOT NULL,

  CONSTRAINT PK_Fileds PRIMARY KEY(id)

);

CREATE TABLE dbo.FieldMappings

(

  thenewid INT NOT NULL, -- related to dbo.Fields(id)

  theoldid INT NOT NULL, -- related to dbo.Fields(id)

  CONSTRAINT PK_FiledMappings PRIMARY KEY (thenewid, theoldid)

);

GO

INSERT into dbo.Forms(name) VALUES('TestForm');

INSERT INTO dbo.Fields(formid, name, value) VALUES

  (1, 'FieldA', 'ValueA'),

  (1, 'FieldB', 'ValueB'),

  (1, 'FieldC', 'ValueC');

GO

SELECT * FROM dbo.Forms;

SELECT * FROM dbo.Fields;

SELECT * FROM dbo.FieldMappings;

id  name

--- ---------

1   TestForm

id  formid  name    value

--- ------- ------- -------

1   1       FieldA  ValueA

2   1       FieldB  ValueB

3   1       FieldC  ValueC

thenewid  theoldid

--------- ---------

You are given some source formid in a parameter called @source_formid:

DECLARE @source_formid AS INT = 1;

Create a new form by inserting a row into dbo.Forms with the name 'NewForm' and store the newly generated formid in a variable called @target_formid:

INSERT INTO dbo.Forms(name) VALUES('NewForm');

DECLARE @target_formid AS INT = SCOPE_IDENTITY();

The challenge:

Part 1)

a)    Create a copy of all the rows related to the form represented by @source_formid in the table dbo.Fields, but now linking them to the newly created form represented by @target_formid.

b)    Insert into the dbo.FieldMappings table rows that associate the source field ids with the target ones.

Both points a and b must be completed in a single T-SQL statement; no triggers involved.

Desired result after running your statement:

SELECT * FROM dbo.Forms;

SELECT * FROM dbo.Fields;

SELECT * FROM dbo.FieldMappings;

id  name

--- ---------

1   TestForm

2   NewForm

id  formid  name    value

--- ------- ------- -------

1   1       FieldA  ValueA

2   1       FieldB  ValueB

3   1       FieldC  ValueC

4   2       FieldA  ValueA

5   2       FieldB  ValueB

6   2       FieldC  ValueC

thenewid  theoldid

--------- ---------

4         1

5         2

6         3

Part 2)

Run the following code to create foreign keys and then try to solve the same challenge:

ALTER TABLE dbo.Fields

  ADD CONSTRAINT FK_Fields_Forms FOREIGN KEY(formid)

  REFERENCES dbo.Forms(id);

ALTER TABLE dbo.FieldMappings

  ADD CONSTRAINT FK_FieldMappings_Fileds_1 FOREIGN KEY(thenewid)

  REFERENCES dbo.Fields(id);

ALTER TABLE dbo.FieldMappings

  ADD CONSTRAINT FK_FieldMappings_Fileds_2 FOREIGN KEY(theoldid)

  REFERENCES dbo.Fields(id);

I managed to find a solution to Part 1, but haven’t yet found a solution to Part 2.

Good luck!

--

BG