Here you go ... hope this helps ...

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#Question','U') IS NOT NULL

DROP TABLE #Question

--===== Create the test table with

CREATE TABLE #Question

(

QuestionID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

SurveyID INT NULL -- Foreign key to the survey (not shown here)

ParentQuestionId INT NULL,

QuestionText VARCHAR(255) NULL,

CampaignId INT NULL -- Foreign key to the campaign (not shown here)

)

--===== All Inserts into the IDENTITY column

SET IDENTITY_INSERT #Question ON

--===== Insert the test data into the test table

INSERT INTO #Question

(QuestionID, SurveyID, ParentQuestionId, QuestionText)

SELECT 1, 1, NULL, 'What is your current system?' UNION ALL

SELECT 2, 1, NULL, 'How old is it' UNION ALL

SELECT 3, 2, NULL, 'How much do you spend each week?' UNION ALL

SELECT 4, 2, NULL, 'Do you use budgeting software?' UNION ALL

SELECT 5, 2, NULL, 'What do you use at the moment?' UNION ALL

SELECT 6, 3, NULL, 'Do you run AV software?'

--===== Set the identity insert back to normal

SET IDENTITY_INSERT #Question ON

Expected result below, after migration. This shows the original questions, along with the new copies of those questions. The original questions have been updated with the QuestionIDs of the new questions.

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#Question','U') IS NOT NULL

DROP TABLE #Question

--===== Create the test table with

CREATE TABLE #Question

(

QuestionID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

SurveyID INT NULL -- Foreign key to the survey (not shown here)

ParentQuestionId INT NULL,

QuestionText VARCHAR(255) NULL,

CampaignId INT NULL -- Foreign key to the campaign (not shown here)

)

--===== All Inserts into the IDENTITY column

SET IDENTITY_INSERT #Question ON

--===== Insert the test data into the test table

INSERT INTO #Question

(QuestionID, SurveyID, ParentQuestionId, QuestionText, CampaignId)

SELECT 1, 1, 7, 'What is your current system?', NULL UNION ALL

SELECT 2, 1, 8, 'How old is it', NULL UNION ALL

SELECT 3, 2, 9, 'How much do you spend each week?', NULL UNION ALL

SELECT 4, 2, 10, 'Do you use budgeting software?', NULL UNION ALL

SELECT 5, 2, 11, 'What do you use at the moment?', NULL UNION ALL

SELECT 6, 3, 12, 'Do you run AV software?', NULL UNION ALL

SELECT 7, NULL, NULL, 'What is your current system?', 21 UNION ALL

SELECT 8, NULL, NULL, 'How old is it', 21 UNION ALL

SELECT 9, NULL, NULL, 'How much do you spend each week?', 22 UNION ALL

SELECT 10, NULL, NULL, 'Do you use budgeting software?', 22 UNION ALL

SELECT 11, NULL, NULL, 'What do you use at the moment?', 22 UNION ALL

SELECT 12, NULL, NULL, 'Do you run AV software?', 23

--===== Set the identity insert back to normal

SET IDENTITY_INSERT #Question ON