dbo.Question
 - QuestionId INT NOT NULL IDENTITY(1, 1)
 - ParentQuestionId INT NULL
 - IsForCampaign BIT NOT NULL DEFAULT (0)

--===== 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

--===== 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

create table #LegacySurvey
(
	SurveyID int identity,
	SurveyName varchar(10)
)

create table #LegacySurveyQuestion
(
	SurveyQuestionID int identity,
	SurveyID int,
	Question varchar(50)
)

insert #LegacySurvey
select 'Survey 1' union all
select 'Survey 2'

insert #LegacySurveyQuestion
select 1, 'Question 1' union all
select 1, 'Question 2' union all
select 2, 'Question 1' union all
select 2, 'Question 2'

create table #NewSurvey
(
	SurveyID int identity,
	SurveyName varchar(50)
)

create table #NewSurveyQuestion
(
	SurveyQuestionID int identity,
	SurveyID int,
	Question varchar(50)
)

Insert #NewSurvey 
select 'This one is already taken' union all
select 'This ons is also already taken'

insert #NewSurveyQuestion
select 1, 'New Question 1' union all
select 1, 'New Question 2' union all
select 2, 'New Question 1' union all
select 2, 'New Question 2'

--The above is intended to represent the new destination table that is already populated with some data. 
--Everything above here is setting up your current situation. You have a source table and a destination table
--both populated with some rows and you need to move the Survey and Questions to the new table and be able to
--keep the existing relational key intact but with new values.

--The easiest way to handle this is to add Legacy Columns to the new table.
Alter table #NewSurvey
add LegacySurveyID int --This lets us know the SurveyID we came from

Alter table #NewSurveyQuestion
add LegacySurveyID int --This lets us know the SurveyID we came from

--We need to populate the LegacySurveyID with the current SurveyID
insert #NewSurvey (SurveyName, LegacySurveyID)
select SurveyName, SurveyID from #LegacySurvey

--We need to populate the LegacySurveyID with the current SurveyID
insert #NewSurveyQuestion(Question, LegacySurveyID)
select Question, SurveyID from #LegacySurveyQuestion

--Now we just change the value of SurveyID using the legacy values from both tables
update #NewSurveyQuestion
set SurveyID = s.SurveyID
from #NewSurveyQuestion q
join #NewSurvey s on q.LegacySurveyID = s.LegacySurveyID

--Next we drop the temporary columns used to hold our mapping
alter table #NewSurvey
drop column LegacySurveyID

alter table #NewSurveyQuestion
drop column LegacySurveyID

--This is the old data to use as comparison for the new versions below
Select * 
from #LegacySurvey s
join #LegacySurveyQuestion q on q.SurveyID = s.SurveyID

--If all worked as expected we should see the previously existing surveys PLUS the newly inserted ones.
--They should all be matched correctly to the right parent.
select * 
from #NewSurvey s
join #NewSurveyQuestion q on q.SurveyID = s.SurveyID

drop table #LegacySurvey
drop table #LegacySurveyQuestion
drop table #NewSurvey
drop table #NewSurveyQuestion

QuestionID ParentQuestionId
---------- ----------------
 1          NULL

QuestionID ParentQuestionId
---------- ----------------
 1          7
 7          NULL