| Author |
Topic |
|
ddobbertin
Yak Posting Veteran
51 Posts |
 Posted - 2010-11-18 : 14:02:43
|
| Trying to use a variable in a table name since the tables are created 4 times a day.I'd like to write a query for a DTS package that will account for the changing table names.Example table names using yesterday and todays date:T_S1117T_D1117T_T1117T_N1117T_S1118T_D1118T_T1118T_N1118Example query:Select * from T_S1117 Looking for something like....Declare @shift charIf datepart(hh,getdate()) between '00' and '08'BeginSet @shift = 'S'EndSet @sDay = datepart(dd,getdate())Set @sMonth = datepart(mm,getdate())Select * from T_+ @shift + @month + @day |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
|
|
ddobbertin
Yak Posting Veteran
51 Posts |
Posted - 2010-11-18 : 14:58:30
|
| First off..thanks...but can you help with the syntax? I think I am having an issue with the concatenating the table name with the variable. Probably the wrong single quotes or something. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
ddobbertin
Yak Posting Veteran
51 Posts |
Posted - 2010-11-18 : 15:17:16
|
| Why are the "gurus" always so E-cocky? You don't have all the information...I'm working with what is given to me and controlled on a corporate level...out of my hands. Maybe offer a suggestion rather than a comment such as the one you left?BTW...thanks for the information....appreciated....almost. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-11-18 : 17:36:34
|
quote:
Originally posted by ddobbertin
Why are the "gurus" always so E-cocky? You don't have all the information...I'm working with what is given to me and controlled on a corporate level...out of my hands. Maybe offer a suggestion rather than a comment such as the one you left?BTW...thanks for the information....appreciated....almost.
Does the management know how bad this is and why? If not, you can do what I have done in the past and refused to do what they ask because they obviously don't understand the issues. Sometimes the CEO says: too bad, play hand you are delt. Other times, management gets bug-eyed and wonder who/how such an abomination was ever implemented. YMMV. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
ddobbertin
Yak Posting Veteran
51 Posts |
Posted - 2010-11-22 : 17:14:09
|
| No it did not....sorry was out of the office a couple days. So what is it that makes the design so bad? I guess I'm a little confused as to how you can tell the entire structure of the database from a couple of table names?And no....I cannot tell you the corporation...lol. |
|
|
|
ddobbertin
Yak Posting Veteran
51 Posts |
Posted - 2010-11-22 : 17:16:50
|
| Also I might have been a little misleading with my original post...which then I could understand where you were coming from with the bad database design. I'm looking to use the variable in a query for the table name. I want to pull the queries 4 times/day and the tables are created for each shift each day. So I want the query dynamic to pull from each of the new tables. I've just been running into an issue with syntax. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2010-11-22 : 17:47:49
|
| Did you try what I gave you?EXEC('Select * from T_' + @shift + @month + @day)Now....look at thisUSE [yourDB]GOSELECT SUBSTRING(TABLE_NAME,1,2) AS TABLE_PREFIX, SUBSTRING(TABLE_NAME,3,1) AS [@shift], SUBSTRING(TABLE_NAME,4,2) AS [@sDay], SUBSTRING(TABLE_NAME,6,2) AS [@sMonth]FROM INFORMATION_SCHEMA.TablesWHERE TABLE_NAME LIKE 'T_%'Now...of you union all of those together you would have the 1 Table it should beBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/ |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
Variable in Table Name?