March 8, 2011 at 7:37 pm
That looks pretty good. Now just 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 14, 2011 at 2:59 pm
It is not working yet, the table in version5_sales is only updated at Admin, not the other 3 values.
March 14, 2011 at 3:01 pm
What is the stored proc code? What parameters are you passing 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 14, 2011 at 3:05 pm
alter procedure [dbo].[WEB_HR_SLXSCREENTEMPLATES](
@version500_prod varchar(50),
@version5_sales varchar(50),
@template_group_1 varchar(50)
@template_group_2 varchar(50)
)
AS
BEGIN
DECLARE @sql varchar(max);
set @sql = 'insert ' + @version5_sales + ' select * from ' + @version500_prod + ' where template_group in (' + @template_group_1 + ')'
END
GO
template_group_1 is the group in version500_prod
template_group_2 is in version5_sales and is getting replaced by 4 values in template_group_1 in version500_prod
March 14, 2011 at 3:08 pm
So you showed me the code for your proc, which still doesn't execute your dynamic sql. You still haven't showed what parameters you are sending in. I can't help you if you don't help me to help you.
_______________________________________________________________
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 14, 2011 at 3:14 pm
if @sql has the sql you want to execute then yes.
In a previous post you said it was only updating one group...
_______________________________________________________________
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 14, 2011 at 3:20 pm
so how should the code look with the execute statement, I apologize for so many questions
March 14, 2011 at 3:24 pm
njdevils39 (3/14/2011)
so how should the code look with the execute statement, I apologize for so many questions
as I said several posts ago..
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 28, 2011 at 4:13 pm
Alter procedure [dbo].[WEB_HR_SLXSCREENTEMPLATES3](
@sourcedb VARCHAR(50),
@targetdb VARCHAR(50),
@sourcegroup VARCHAR(50),
@targetgroup VARCHAR(50),
@sourcedb1 VARCHAR(50),
@targetdb1 VARCHAR(50),
@sourcegroup1 VARCHAR(50),
@targetgroup1 VARCHAR(50),
@sourcedb2 VARCHAR(50),
@targetdb2 VARCHAR(50),
@sourcegroup2 VARCHAR(50),
@targetgroup2 VARCHAR(50),
@sourcedb3 VARCHAR(50),
@targetdb3 VARCHAR(50),
@sourcegroup3 VARCHAR(50),
@targetgroup3 VARCHAR(50)
)
AS
BEGIN
declare @sql varchar(max)
declare @sql1 varchar(max)
declare @sql2 varchar(max)
declare @sql3 varchar(max)
set @sql = 'select * from ' + @sourceDB + '..slxhrscreentemplates where template_group = ''' + @sourceGroup + ''''
set @sql1 = 'select * from ' + @sourceDB1 + '..slxhrscreentemplates where template_group = ''' + @sourceGroup1 + ''''
set @sql2 = 'select * from ' + @sourceDB2 + '..slxhrscreentemplates where template_group = ''' + @sourceGroup2 + ''''
set @sql3 = 'select * from ' + @sourceDB3 + '..slxhrscreentemplates where template_group = ''' + @sourceGroup3 + ''''
EXEC(@sql)
EXEC(@sql1)
EXEC(@sql2)
EXEC(@sql3)
-- select * from Stiven..slxhrscreentemplates
Create Table #slxhrscreentemplates2
(template_id int,
template_name varchar(255),
template_content XML,
template_group varchar(255),
template_section varchar(255),
delete_ind int,
[application] varchar(255)
)
insert into #slxhrscreentemplates2
(template_id, template_name, template_content, template_group, template_section, delete_ind, [application])
select template_id, template_name, template_content, template_group, template_section, delete_ind, [application]
from version500_prod..slxhrscreentemplates where template_group in ('Admin','Data Proc','Corp Benefits','Smartlinx')
order by template_group
select * from #slxhrscreentemplates2
END
GO
----
Ok so this is what I have and it works, but I need to update the @targetDB and the @targetGroup Corresponding to the 4 values.
So What is the correct update statement
@sourceDB and all the corresponding @SourceDB = version500_prod
@targetDB and all the corresponding @targetDB = Stiven
@sourceGroup = Admin
@sourceGroup1 = Corp Benefits
@sourceGroup2 = Data Proc
@sourceGroup3 = Smartlinx
@targetGroup1 = Admin
@targetGroup2 = Manager
@targetGroup3 = Payroll
@targetGroup4 = HR
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply