| Author |
Topic |
|
nemohm
Yak Posting Veteran
69 Posts |
 Posted - 2003-05-01 : 16:25:40
|
| Hi,I have a new table that is generated every day, and these tables always have the same definition.How can I specify the name of the table at runtime?Dynamic SQL is the tool?How to implement it when the name of the table is in sysobjects, and need everything from a table created yesterday?Select *From (SELECT name FROM sysobjects WHERE crdate>DATEADD(day, -1,GETDATE())The subquery returns the right table name. (I’m not familiar with dynamic SQL, neither with advanced SQL)Thx,Dobby |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-01 : 16:28:06
|
| Always do an article search and a forum search to see if your question has already been answered:The answer is at the bottom of this article (although the entire article is worth a read, also see Part 2 of the article):[url]http://www.sqlteam.com/item.asp?ItemID=4599[/url]Part 2:[url]http://www.sqlteam.com/item.asp?ItemID=4619[/url]Tara |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-05-01 : 16:31:47
|
Avoid dynamic SQL if you can! read the articles Tara referenced for informational purposes, but consider your design before applying dynamic sql to your problem.quote:
I have a new table that is generated every day, and these tables always have the same definition.
can you give some insight as to why? why not just add rows to an existing table, and date_stamp those rows? Then, you have none of these issues, you can compare data between days very easily, etc.So, if your data has a primary key of "ID", instead of all of these tables:Day1_Data Day2_Data Day3_Data..etc..you have 1 table called "Data" with a primary key of ("Day", "ID"). Each day when you insert data, you insert data into a column which marks that set of data as belonging to that particular day.I hope this makes some sense!- JeffEdited by - jsmith8858 on 05/01/2003 16:33:16 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-01 : 16:36:06
|
| Jeff,That's a much better solution than dynamic sql! I usually just answer the questions here without even thinking of an alternative. I really should think of an alternative though especially in situations like this.Tara |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-05-01 : 16:56:06
|
I think we should penalize you 20 posts for answering too quickly! (I can't believe you are reaching 1000 before me ... it seemed I was WAY ahead of you not too long ago ...) - Jeff |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-01 : 17:01:50
|
quote:
I think we should penalize you 20 posts for answering too quickly!
Ah, but I answered the question! I just didn't let him know that dynamic sql shouldn't be used in most scenarios.quote:
(I can't believe you are reaching 1000 before me ... it seemed I was WAY ahead of you not too long ago ...)- Jeff
You were way ahead of me, but that was when I was just checking this site occassionally. Now I check it often to help people out and to gain more knowledge to further my career/resume.TaraEdited by - tduggan on 05/01/2003 17:02:11 |
|
|
|
nemohm
Yak Posting Veteran
69 Posts |
Posted - 2003-05-01 : 17:13:46
|
| Jeff,Tables are created every day by an application. There is nothing Ican do about the structure of the DB.Once a day I need to run a scrip that gets entire table, created yesterday and DTS it to another DB.Why a subquery in FROM doesn't work and is there a solution?Thx,Dobby |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-05-02 : 12:28:56
|
| I think your application developers need to learn how to insert data into tables instead of creating new tables constantly.If they're creating database objects, why not have them create a VIEW every day as well (or ALTER the same view) which always has the same name and references that current day's table? then you are all set, just reference the daily table through the view.So, if each day they create a table:Data_12-1-2002or something like that, every day have them run this command:execute "ALTER VIEW Data AS SELECT * FROM [" & Todays_Table_Name & "]"the app must know the table name, since the app is creating it! But they really should be adding data to the same table....Otherwise, read up on the dynamic sql links that Tara provided. but I would definitely make this process simplier if you can, not more complex.- Jeff |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-05-02 : 12:34:28
|
It's a shape shifter database!Nice |
|
|
|
|
Dynamic SQL ?