SP Help 4 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SP Help 4

Author

Topic

steven.liberman1
Starting Member

15 Posts

Posted - 2011-03-08 : 11:03:16

source database: version500
target database: version5_sales
source version500..template_group
target version5_sales..template_group

select * from version500..slxhrscreentemplates where template_group in ('ADMIN','Manager','Payroll','HR')

select * from version5_sales..slxhrscreentemplates where template_group in ('ADMIN','Corp Benefits','Data Proc','Smatlinx')

1. Source DB
2. Target DB
3. Source template group name
4. Target template group name

All these would be varchar(50)

So what you need to do is the following:

1. From the source DB read the template content for each template name where template_group = source template group name

2. Update template content in the target DB for the target group name corresponding to the template name which you read in the above loop

Now what you need here is no hardcoding of database name or group name but you will have to write dynamic sql statements like -

declare @sql varchar(max)

set @sql = 'select * from ' + @sourceDB + '..slxhrscreentemplate where group_name = ''' + @sourceGroup + '''

and execute this @sql by-

exec @sql

This will give you all template rows for the source set.
Now similar way you can write dynamic sql statements for what you need to do and execute them to get resultset and also update statements in a similar way.

this is what I have so far:

create procedure WEB_HR_ScreenTemplateUpdate {
@version500 varchar(50)
@version5_sales varchar(50)
@version500..slxhrscreentemplates.template_group varchar(50)
@version5_sales..slxhrscreentemplates.template_group varchar(50)
}
as

-- select * from version500..slxhrscreentemplates where template_group in ('ADMIN','Manager','Payroll','HR')

-- select * from version5_sales..slxhrscreentemplates where template_group in ('ADMIN','Corp Benefits','Data Proc','Smatlinx')

Begin

set @sql = 1;
declare @sql varchar(max)

set @sql = 'select * from ' + @version500 + '..slxhrscreentemplate where template_group =
'(ADMIN, Manager , Payroll, HR)' + @version500..slxhrscreentemplates.template_group + '''

End
Go

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-08 : 11:47:13

Are you getting any error or you have posted the script here for your future reference ?

steven.liberman1
Starting Member

15 Posts

Posted - 2011-03-08 : 17:18:16

Basically I have to write a stored procedure using dynamic sql

to

update the slxhrscreentemplates table in version5_sales

with the information from the

slxhrscreentemplates in version500

I have 4 parameters:

1. source Database = version500
2. target Database = version5_sales
3. source group = template_group
4. target group = template _group

So I have to insert this query

select * from version500..slxhrscreentemplates where template_group in ('ADMIN','Manager','Payroll','HR')

into

select * from version5_sales..slxhrscreentemplates where template_group in ('ADMIN','Corp Benefits','Data Proc','Smatlinx')

remember template_content is XML, so I have to preserve the XML

I have to pass all the parameters and use the @sql string.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-03-09 : 10:56:45

why you want to go for dynamic query? why does your objects change at runtime?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

steven.liberman1
Starting Member

15 Posts

Posted - 2011-03-10 : 11:40:34

That was the instruction I received at the office, so I need a dynamic query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources