Assuming my understanding posted above is correct here is an example of doing this type of thing without using a cursor. What you have to do is add a column in the destination tables to hold the current PK values. Then you can use those values and update accordingly. I hobbled together a simplified version based on your problem. See if this is doing what you are trying to do.

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