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