Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic SQL to move data from from database to another taking into account identity keys


Dynamic SQL to move data from from database to another taking into account identity keys

Author
Message
Gregory Brown
Gregory Brown
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 80
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,
GregUnsure
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2928 Visits: 4076
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;-)
Gregory Brown
Gregory Brown
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 80
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)


Gregory Brown
Gregory Brown
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 80
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search