March 14, 2011 at 9:52 am
Ok, so instead of dynamic sql,
how would I write a stored procedure using hard coding to update slxhrscreentemplates table?
Source Database: version500_prod
Target Databse: version5_sales
Source Table: slxhrscreentemplates in version500_prod
Target Table: slxhrscreentemplates in version5_sales
I have to replace
select template_id, template_name, template_content, template_group, template_section,
delete_ind, application
from version5_sales..slxHRScreenTemplates
where template_group = 'ADMIN' or template_group = 'Corp Benefits' or template_group = 'Data Proc' or template_group = 'Smartlinx'
with this
select template_id, template_name, template_content, template_group, template_section,
delete_ind, application
from version500_prod..slxHRScreenTemplates
where template_group = 'ADMIN' or template_group = 'Manager' or template_group = 'Payroll' or template_group = 'HR'
Once again template_content is XML declared so I have to preserve XML
I need the parameters.
March 14, 2011 at 10:09 am
You don't have an update here. What are you trying to update from where? The two SELECT's don't explain what you are trying to do.
March 14, 2011 at 10:14 am
I have to update the slxhrscreentemplates table in version5_sales
where the template_group =(Admin, Corp Benefits, Data Proc, and Smartlinx)
with the information from version500_prod
where template_group = (Admin, Manager, Payroll, HR)
Just Keep in mind that template_content is XML so I have to preserve XML.
March 14, 2011 at 2:08 pm
This is the third of fourth thread where you are basically asking the same thing over and over. People keep asking for details and the best we get is extremely vague. We can't see your screen and have no knowledge of your project. Read the link in my signature to learn how to post a question in such a way that we are able to help. I am pretty sure that you looked at the last thread were I was helping you that you could pretty quickly find the sql that would do this exactly as you asked. You can find it here.
set @sql = 'insert ' + @version5_sales + ' select * from ' + @version500 + ' where template_group in (' + @template_group + ')'
Just replace your variable with your hardcoded values.
_______________________________________________________________
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:40 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 + ')'
+ '' template_group_1 = '('Admin','Manager','Payroll','HR')'
END
GO
This is what I have so far
March 14, 2011 at 2:42 pm
You said you don't want dynamic sql but that is what this is. Do you want dynamic sql or not?
_______________________________________________________________
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:43 pm
Yes, give me in dynamic sql and I need an execute statement, but make sure all 4 template_group values are replaced
March 14, 2011 at 2:52 pm
Go look in the other thread. It was all there. You just need to execute your dynamic string.
_______________________________________________________________
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/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply