March 8, 2011 at 9:29 am
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
March 8, 2011 at 9:32 am
What is the question here?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 8, 2011 at 9:36 am
I need to modify the slzhrscreentemplates table in version5_sales with the information from version500 slxhrscreentemplates table.
I need to pass the 4 parameters
the source and target dbases
March 8, 2011 at 9:46 am
Keep in mind that we can't see your screen and we are not familiar with your project. I still have no idea what you are trying to do here.
One quick piece of advice that will make you life about a billion times easier is to make your variable names a bit shorter.
@version500..slxhrscreentemplates.template_group
This is way too long to understand what it is and it contains invalid characters in a variable name.
Maybe some like @version500_templates or something along those lines.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 8, 2011 at 1:57 pm
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.
March 8, 2011 at 2:05 pm
njdevils39 (3/8/2011)
Basically I have to write a stored procedure using dynamic sqlto
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.
So you just need to take the records from version500 and insert them into version5_sales? I am not sure what you mean by
remember template_content is XML, so I have to preserve the XML
but i assume that is a column of type xml? There is nothing special you have to do here just because it is xml.
This sounds like all you need to do is:
insert [destination table]
select [source table]
or in your case
insert version5_sales
select * from version500..slxhrscreentemplates where template_group in ('ADMIN','Manager','Payroll','HR')
I realize you need to make this dynamic but that is the basic syntax. Now all you have to is populate @sql with this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 8, 2011 at 2:25 pm
yes, I have to insert
select * from version500..slxhrscreentemplates where template_group in ('ADMIN','Manager','Payroll','HR')
into version5_sales
where
version5_sales =
select * from version5_sales..slxhrscreentemplates where template_group in ('ADMIN','Corp Benefits','Data Proc','Smatlinx')
but I have to use dynamic sql
March 8, 2011 at 2:33 pm
You showed using dynamic sql in your first post. Just modify that insert to use variable names instead of the hardcoded ones and execute it.
set @sql = 'insert ' + @Destination + ' select * from ' + @Source + ' where template_group in (' + @SourceGroup + ')'
I have to say I don't understand the point of the target group parameter.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 8, 2011 at 3:02 pm
Could you show me an example of a stored proc for this?
March 8, 2011 at 3:06 pm
you pretty much did it above...
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
just change your variable names to ones that work and use the insert.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 8, 2011 at 3:22 pm
alter procedure [dbo].[WEB_HR_ScreenTemplatesUpdate] (
@version500 varchar(50),
@version5_sales varchar(50),
@template_group varchar(50)
)
AS
BEGIN
-- select * from version500..slxhrscreentemplates where template_group in ('ADMIN','Manager','Payroll','HR')
-- select count(template_id) from version500..slxhrscreentemplates
-- select * from version5_sales..slxhrscreentemplates where template_group in ('ADMIN','Corp Benefits','Data Proc','Smatlinx')
-- select count(template_id) from version5_sales..slxhrscreentemplates
declare @sql varchar(max)
set @sql = 'insert ' + @version5_sales + ' select * from ' + @version500 + ' where template_group in ('ADMIN','Manager','Payroll','HR')' + @template_group + ')'
end
This is what I have so far?
March 8, 2011 at 3:25 pm
I don't think you want the hardcode group, that is why you are passing it in. Other than you just need to execute it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 8, 2011 at 3:30 pm
Please Give me a good execute statement to make it work then
March 8, 2011 at 3:33 pm
take a look at sp_executesql
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 8, 2011 at 3:48 pm
-- Create date: February 25, 2011
-- Description:update Version5_sales template Groups
-- exec [WEB_HR_ScreenTemplatesUpdate] 1,2, 3
-- ================================================================
alter procedure [dbo].[WEB_HR_ScreenTemplatesUpdate] (
@version500 varchar(50),
@version5_sales varchar(50),
@template_group varchar(50)
)
AS
BEGIN
-- select * from version500..slxhrscreentemplates where template_group in ('ADMIN','Manager','Payroll','HR')
-- select count(template_id) from version500..slxhrscreentemplates
-- select * from version5_sales..slxhrscreentemplates where template_group in ('ADMIN','Corp Benefits','Data Proc','Smatlinx')
-- select count(template_id) from version5_sales..slxhrscreentemplates
declare @sql varchar(max)
set @sql = 'insert ' + @version5_sales + ' select * from ' + @version500 + ' where template_group in (' + @template_group + ')'
end
This is what I have
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply