October 9, 2013 at 1:47 pm
I'm trying to move specific data from three linked tables on a source database to three tables on a destination database by generating dynamic SQL INSERT scripts but am getting stuck on the last set of INSERT statements. I don't think I can use SSIS because the source autonumber fields may already exist on the destination side but I could be wrong.
For simplicity, Table 1 (T1) has one autonumber key of PK1 as well as other fields (A1, B1, etc.).
Table 2 (T2) has one autonumber key of PK2 and a foreign key (FK1) that links back to PK1 as well as other fields (A2, B2, etc.).
Table 3 (T3) has one autonumber key of PK3 and a foreign key (FK2) that links back to PK2 as well as other fields (A3, B3, etc.).
Like this:
T1: PK1, A1, B1, etc.
T2: PK2, FK1, A2, B2, etc.
T3: PK3, FK2, A3, B3, etc.
So, I'm able to query the source database T1 to generate my dynamic SQL INSERT statements that will be run on the destination side. I'm also able to generate my dynamic SQL statements to insert into T2 but when I get to T3 I currently am stuck figuring out how to insert because the destination side is unable to match it's FK2 to the just inserted PK2. It throws the below error.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Is what I'm trying to do not possible? Is there another way to accomplish this?
Thanks,
Greg:unsure:
October 10, 2013 at 3:51 am
you error shows that multiple recrds are being returned from inner query.
please post the culprit query.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 10, 2013 at 6:31 am
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)
October 10, 2013 at 6:43 am
Scratch that...my idea of storing anything in memory obviously won't work because there is no shared memory between the source and destination since all I'm delivering to the destination is a set of INSERT scripts to run.
I may have to script out the contents of my source table as a separate insert into a temp table on the destination so that I can then add the crosswalk key from the newly inserted table...doesn't sound fun.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply