question

palum avatar image
palum asked

tsql scripting code

I am having a tedious iterative process. I need something easier process. Please suggest I have 200 update statements something like Update std771.rollnumber SET ID = '1234' , Sub_ID ='3333' where studentadmdate between '1/1/2010' and '2/1/2010' and studentid = '1164' Update std771.rollnumber SET ID = '1209' , Sub_ID ='7777' where studentadmdate between '1/1/2010' and '2/1/2010' and studentid = '1190' I basically need to change the table names of both the statement from rollnumber to student1 and student2 respectively. Giving me 4 outputs something like Update std771.student1 SET ID = '1234' , Sub_ID ='3333' where studentadmdate between '1/1/2010' and '2/1/2010' and studentid = '1164' Update std771.student1 SET ID = '1209' , Sub_ID ='7777' where studentadmdate between '1/1/2010' and '2/1/2010' and studentid = '1190' Update std771.student2 SET ID = '1234' , Sub_ID ='3333' where studentadmdate between '1/1/2010' and '2/1/2010' and studentid = '1164' Update std771.student2 SET ID = '1209' , Sub_ID ='7777' where studentadmdate between '1/1/2010' and '2/1/2010' and studentid = '1190' I do not want to script these statements out manually since i have 200 statements, which will be 200x4=800. Please suggest.
sql-server-2005t-sql
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
For each of the answers that are helpful, please indicate that by clicking on the thumbs up sign next to them. If any one answer solved your problem, please indicate that by clicking on the check mark next to it. You have 28 questions and you've only accepted a very few answers. The site works on those votes. Please help out by voting up the helpful answers and marking the correct ones.
0 Likes 0 ·
Mister Magoo avatar image
Mister Magoo answered
This seems a bit obvious, but Find and Replace works for me.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
I would suggest looking into using dynamic T-SQL and then execute the statements using [sp_executesql][1]. If you have to do hundreds of distinct statements I would think that's a better approach. Or, just take a programmatic approach and use PowerShell or something to build the statements based on a set of variables. Although, from your question and your examples, I think your design might be a little off. It seems like you're making some odd choices in terms of table names and update statements. [1]: http://msdn.microsoft.com/en-us/library/ms188001.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.