Hopefully the below helps. Thanks.

T1 = docket_module whose PK is docket_module_ID (step 1 below)

T2 = docket_module_step whose PK is an autonumber field called docket_module_step_ID and FK of docket_module_ID back to T1 (step 3 below)

T3 = docket_module_input_param whose PK is an autonumber field with FK of docket_module_step_ID back to T2 (step 4 below which is where I get the error)

The problem I'm having is how to do the insert into T3 using the correct FK back to T2. I'm thinking I may need to store the contents of my insert into T2 in memory along with all of the SOURCE database's fields for T2 so that I can then later match them up when I do the insert into T3 on the DESTINATION. Otherwise, my inserts into T3 on the destination side don't know which FK to use.

DECLARE @dmChangedBy int = 1 --The user_ID

DECLARE @dmChangedDate datetime = GetDate()

--1) INSERT INTO docket_module

DECLARE @dmTitle varchar(50) = 'LOOP YES NO CREATE ACTIVE PROCESS'

select 'INSERT INTO docket_module (docket_module_title,docket_module_description,docket_module_category_ID,docket_module_enabled,changed_date,changed_by) VALUES ('

+ '''' + docket_module_title + ''','

+ '''' + isnull(docket_module_description,'') + ''','

+ isnull(RTRIM(LTRIM(STR(docket_module_category_ID))) + ''',','NULL,')

+ isnull(RTRIM(LTRIM(STR(docket_module_enabled))) + ',','NULL,')

+ '''' + RTRIM(isnull(cast(@dmChangedDate as varchar(30)) + ''',','NULL,'))

+ LTRIM(RTRIM(STR(@dmChangedBy)))

+ ')' as '1st SQL statement - docket_module'

from docket_module

where docket_module_title = @dmTitle

--2) INSERT INTO docket_module_variable

--First get docket_module_ID for newly inserted docket module to use in rest of WHERE clauses

-- NOTE: This variable declaration and assignment has to be scripted so that it occurs on the DESTINATION database not the source database so as not to have a key conflict

--DECLARE @dmDocketModuleID int

--select 'DECLARE @dmDocketModuleID int = (select docket_module_ID from docket_module where docket_module_title=@dmTitle)'

select 'INSERT INTO docket_module_variable (docket_module_ID,step_GUID,param_name,variable_name,fully_qualified_type,changed_date,changed_by) VALUES ('

+ RTRIM(('(select docket_module_ID from docket_module where docket_module_title=''' + @dmTitle + ''')')) + ','

+ '''' + isnull(step_GUID,'') + ''','

+ '''' + isnull(param_name,'') + ''','

+ isnull(cast('''' + variable_name + ''',' as varchar(2000)),'NULL,')

+ '''' + isnull(fully_qualified_type,'') + ''','

+ '''' + RTRIM(isnull(cast(@dmChangedDate as varchar(30)) + ''',','NULL,'))

+ LTRIM(RTRIM(STR(@dmChangedBy)))

+ ')' as '2nd SQL statement - docket_module_variable'

from docket_module_variable

where docket_module_ID = (select docket_module_ID from docket_module where docket_module_title=@dmTitle) --@dmDocketModuleID

--3) INSERT INTO docket_module_step

select 'INSERT INTO docket_module_step (docket_module_ID,module_action,step_GUID,step_number,changed_date,changed_by) VALUES ('

+ RTRIM(('(select docket_module_ID from docket_module where docket_module_title=''' + @dmTitle + ''')')) + ','

+ '''' + isnull(module_action,'') + ''','

+ '''' + isnull(step_GUID,'') + ''','

+ '''' + isnull(RTRIM(LTRIM(STR(step_number))) + ''',','NULL,')

+ '''' + RTRIM(isnull(cast(@dmChangedDate as varchar(30)) + ''',','NULL,'))

+ LTRIM(RTRIM(STR(@dmChangedBy)))

+ ')' as '3rd SQL statement - docket_module_step'

from docket_module_step

where docket_module_ID = (select docket_module_ID from docket_module where docket_module_title=@dmTitle) --@dmDocketModuleID

--4) INSERT INTO docket_module_input_param

select 'INSERT INTO docket_module_input_param (docket_module_step_ID,param_name,source_type,expression,fully_qualified_type,changed_date,changed_by) VALUES ('

+ RTRIM('(select docket_module_step_ID from docket_module_step where docket_module_ID in ((select docket_module_ID from docket_module where docket_module_title =''' + @dmTitle + ''')))') + ','

+ '''' + isnull(param_name,'') + ''','

+ '''' + isnull(source_type,'') + ''','

+ '''' + isnull(RTRIM(LTRIM(expression)) + ''',','NULL,')

+ '''' + isnull(RTRIM(LTRIM(fully_qualified_type)) + ''',','NULL,')

+ '''' + RTRIM(isnull(cast(@dmChangedDate as varchar(30)) + ''',','NULL,'))

+ LTRIM(RTRIM(STR(@dmChangedBy)))

+ ')' as '4th SQL statement - docket_module_input_param'

from docket_module_input_param

--where docket_module_input_param.docket_module_step_ID in (select docket_module_step_ID from docket_module_step where docket_module_ID = ('(select docket_module_ID from docket_module where docket_module_title =''' + @dmTitle + ''')')) --@dmDocketModuleID)

where docket_module_input_param.docket_module_step_ID in (select docket_module_step_ID from docket_module_step where docket_module_ID in ((select docket_module_ID from docket_module where docket_module_title = @dmTitle ))) --@dmDocketModuleID)