April 30, 2010 at 11:48 am
Sorry if this is not enough detail or doesn't follow the format (tried to do as best I could from the forum etiquette rules) and this is my first post.
Quick background - Each month I have to load billing data into monthly tables (item01 for January, item02 for February, item03 for March, etc.)
Each month I have to manually update the queries to change all of the [myDatabase]..[item01] UPDATE statements to coincide with the current month. For example, to update the queries from March to April data, I have to update each statement from [myDatabase]..[item03] to [myDatabase]..[item04]. Normally I just do a find a replace to take care of this, but I'd rather have it completely automated so I can pass this work off to one of my understudies.
Everything has been working great from my CREATE TABLE statements to my INSERT statements. However, I run into this issue once I get to my UPDATE statements because of the single quotes.
Here is an example of the code I'm trying to accomplish (shortened for simplicity):
SET QUOTED_IDENTIFIER ON
DECLARE @theItemTable VARCHAR(50);
SET @theItemTable = '[itemDetail'
SET @theItemTable = '[myTable].[dbo].' + @theItemTable + RIGHT('0' + CONVERT(VARCHAR,DATEPART(MM,GETDATE())),2) + ']'
EXECUTE ('CREATE TABLE '+ @theItemTable +
'(keyedDate VARCHAR(50) NULL,
invoiceDate VARCHAR(50) NULL,
gelogical VARCHAR(50) NULL,
item VARCHAR(75) NULL,
buc VARCHAR(50) NULL,
adn VARCHAR(50) NULL,
serviceType VARCHAR(50) NULL,
vendor VARCHAR(80) NULL,
itemType VARCHAR(80) NULL,
itemDescription VARCHAR(80) NULL,
unknown VARCHAR(50) NULL,
itemCount VARCHAR(50) NULL,
itemCharges FLOAT NULL
)' )
This code works fine and it now allows me to not have to update the month value for each of the tables. My insert statements are all working flawlessly as well with this syntax. The problem arises when I come to my UPDATE statements:
EXECUTE ('UPDATE '+ @theItemTable +
' SET vendor =
CASE
WHEN vendor = 'vendor1'THEN'The Vendor 1'
WHEN vendor = 'vendor2'THEN'The Vendor 2'
WHEN vendor = 'vendor3'THEN'The Vendor 3'
WHEN vendor = 'vendor4'THEN'The Vendor 4'
ELSE vendor
END ')
Because of the single quotes in my SET values, it is cutting the statement off early and not updating my table. I also tried SET QUOTED_IDENTIFIER ON and tried using double quotes around my single quotes, but to no success.
Is there any way to accomplish this with the method I'm attempting, or will I have to manually update the table names each month? This would be HUGE for lowering time consumption on these monthly queries as there are over 300 Find/Replace updates I currently have to do to update the queries to the new month. Any help would be greatly appreciated!
(Sorry if this isn't enough detail - please let me know if you need anything else).
Thanks!!
Danny Sheridan
Comtekh, Inc.
April 30, 2010 at 11:56 am
Danny i think this is just a basic syntax issue;
if you are building a string that ahs a single quote in it, two single quotes in a row signify the "escape" sequence to mean you want a single quote as part of the string:
so for exampel the string you posted should be this:
EXECUTE ('UPDATE '+ @theItemTable +
' SET vendor =
CASE
WHEN vendor = ''vendor1'' THEN ''The Vendor 1''
WHEN vendor = ''vendor2'' THEN ''The Vendor 2''
WHEN vendor = ''vendor3'' THEN ''The Vendor 3''
WHEN vendor = ''vendor4'' THEN ''The Vendor 4''
ELSE vendor
END ')
Lowell
April 30, 2010 at 12:02 pm
Lowell - That was it! For some reason I thought that first single quote would terminate the entire statement and not follow through with the rest of my CASE updates. Sorry if this was an elementary question but I'm a self taught DBA and knowing that I can use this syntax will help me IMMENSELY!
Once again, thanks for your input and for such a quick response!
Thanks!!
Danny Sheridan
Comtekh, Inc.
April 30, 2010 at 12:24 pm
Danny Sheridan (4/30/2010)
Each month I have to load billing data into monthly tables (item01 for January, item02 for February, item03 for March, etc.)
Why would you do that? Use one table with a month number column and you won't have to use dynamic SQL at all.
April 30, 2010 at 12:29 pm
David Portas (4/30/2010)
Why would you do that? Use one table with a month number column and you won't have to use dynamic SQL at all.
excellent point, David. sometimes you need an extra pair of eys to help you look beyond some bad assumptions; David's right,you could make this a lot easier.
Lowell
April 30, 2010 at 12:49 pm
Because that's not the way the system is designed. And querying from our CF front-end application is much faster on a 15,000,000 record table rather than a 180,000,000+ record table. You'd have to understand our system in order to suggest combining them all into one table and you'd understand why it wouldn't work (or how many months of development it would require our developer to adjust his code to accomdate for it).
EDIT: This is the only database these types of tables are created. All of my other tables (that I designed) have a month/year column for populating data rather than seperating them into seperate tables as you suggested. But for this particular application, this method is much more feasible.
Danny Sheridan
Comtekh, Inc.
April 30, 2010 at 1:00 pm
So you have a badly designed application and therefore you have to work around it in the database. Fair enough, we've all been there. 180 million rows is not a reason to have separate tables however. There's no fundamental reason why that shouldn't be in one table.
One thing you could do is combine the data into one table and create views for each month for the application to use. That way you may be able to support the existing app without any modification.
Hope this helps.
April 30, 2010 at 1:02 pm
Lowell (4/30/2010)
David Portas (4/30/2010)
Why would you do that? Use one table with a month number column and you won't have to use dynamic SQL at all.excellent point, David. sometimes you need an extra pair of eys to help you look beyond some bad assumptions; David's right,you could make this a lot easier.
I think it's a stretch calling it a bad assumption. If you work with developers a lot, you should know how tough it is to have them change hundreds of pages of code when a suggestion is made for improving the database structure. In all of my other databases/tables that store monthly data, I use this structure. Being that the application our developer had already been in place, I was pretty much forced to go with this layout (these are low level detail tables as well - the main tables these tie into contain all months' data with the month/year columns you suggested).
Danny Sheridan
Comtekh, Inc.
April 30, 2010 at 1:05 pm
David Portas (4/30/2010)
So you have a badly designed application and therefore you have to work around it in the database. Fair enough, we've all been there. 180 million rows is not a reason to have separate tables however. There's no fundamental reason why that shouldn't be in one table.One thing you could do is combine the data into one table and create views for each month for the application to use. That way you may be able to support the existing app without any modification.
Hope this helps.
I wouldn't say it's badly designed. But before I came onboard and explained how to better our database and table schemas, they pretty much rolled with what was easiest. I appreciate your input and will forward this to our developers! :w00t:
Danny Sheridan
Comtekh, Inc.
April 30, 2010 at 1:11 pm
Dave - Not a bad idea on utilizing Views and combining them into one master table. This still, however, would require MAJOR development cycles as the data is used across dozens of applications.
Once again thanks for your input. I've never seen a database in my life until a year and a half ago and was basically told to figure it out. All of my knowledge comes from trial/error, and all of the great information provided by members like yourself, Lowell, and everyone else in the SSC community (wish I went to college for CS or DB Development/Management :ermm:). Learn something new everyday!
Danny Sheridan
Comtekh, Inc.
April 30, 2010 at 2:01 pm
Danny Sheridan (4/30/2010)
(wish I went to college for CS or DB Development/Management :ermm:). Learn something new everyday!
You could probably count on one hand the number of regulars here who went to college for DB development :cool:. Also, great job for your first post. You could probably use the other hand to count the % of people who read forum etiquette rules BEFORE posting, and we definitely appreciate it.
April 30, 2010 at 3:22 pm
Thanks, Seth! After reading these forums for months, I know how much easier it makes it on those who are willing to help when you have the code at your disposal for debugging purposes, and pose the scenario in which you are trying to accomplish with your code.
And I definitely hear ya on the schooling part - even though I never got a degree, I think I learned just as well by being thrown into my position (literally - landscaping one day; creating queries in Access the next...and I never even heard of Access!) and having my superiors give me the flexibility to learn their infrastructure and go from there. That combined with spending an hour a night reading up on topics unknown to me have helped a great deal and I think would be comparable to some types of schooling I could've gotten.
Sorry for the prolonged post, but I just wanted to say thanks again for all your kind words, support, and input, for helping me improve my database structure and general knowledge overall.
Have a great weekend all!
Danny Sheridan
Comtekh, Inc.
April 30, 2010 at 6:48 pm
Garadin (4/30/2010)
Danny Sheridan (4/30/2010)
(wish I went to college for CS or DB Development/Management :ermm:). Learn something new everyday!You could probably count on one hand the number of regulars here who went to college for DB development :cool:. Also, great job for your first post. You could probably use the other hand to count the % of people who read forum etiquette rules BEFORE posting, and we definitely appreciate it.
I agree with both points Seth made above... especially about the excellent job you did of posting, Danny. Very well done and welcome aboard!
--Jeff Moden
Change is inevitable... Change for the better is not.
April 30, 2010 at 7:05 pm
I have to agree with the others, good start on posting. I did want to point out one thing that I have found helps me out with dynamic SQL and quotes. There is a nice utility that will do the quoting for you.
SELECT quotename('SomeValue') -- defaults to []
SELECT quotename('SomeValue', char(39)) -- single-quotes
SELECT quotename('SomeValue', char(34)) -- double-quotes
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 4, 2010 at 3:30 am
Hi Danny,
1. You can use the parameterized dynamic queries to solve your problem with the dynamic update query.
2. In future if the development project is going for any sort of enhancement please try to implement partition in the database for the historical tables.. You can have all the 180 milloin + values in the same table logically and you can split and store them physically in different files.. And when the developers querying they need to add the partition key values in the WHERE clause to fetch the results faster.
These are my comments from what I have learnt from this site. Experts please correct me if I am wrong... 🙂
Regards,
Rajesh Subramanian
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply