Modifying stored procedure to pick most recent tab

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Modifying stored procedure to pick most recent tab

Author

Topic

Lionheart
Starting Member

41 Posts

Posted - 2008-07-09 : 14:53:23

Hi All. First post, so am going to start off simple.

I have a stored procedure that I need to modify to automatically pull the most recent set of results from an analysis.

Each month I need to carry out a run, which produces a series of tables, all with the base to the name "TblResult_*****" where the stars change for each analysis (each month). Extra information is stored in tables such as

TblResult_*****
TblResult_*****_Mon
TblResult_*****_Tue
TblResult_*****_Etc

At present the code is


select top 1 @DataTable= table_name   
from        DBSERVER.Results_Table.INFORMATION_SCHEMA.Tables   
where TABLE_TYPE =        'BASE TABLE' and len(table_name)=15

In the past the old analysis tables have been deleted and replaced with new tables. The script picks the first table because it has 15 characters in the name.

How can I add to the script to select the most recently created table that has 15 characters in the name?

Thanks,

LH

mfemenel
Professor Frink

1421 Posts

Posted - 2008-07-09 : 14:58:23

Sysobjects would give you the create date and then it should be simple from there no?

Mike
"oh, that monkey is going to pay"

Lionheart
Starting Member

41 Posts

Posted - 2008-07-09 : 15:06:52

I access the create date, but was hoping someone might be able to help with the code required to start looking at the tables from most recent until it reaches the first one which is 15 characters in length.

Of note, I am very beginner level at this...thrown in at deep end.

mfemenel
Professor Frink

1421 Posts

Posted - 2008-07-09 : 15:23:19

Let me ask something to clarify. So you have any number of tables that start off with tblResult_ and then the analysis name and each day the old one is appended with the day of the week like _MON when the new one is created? And you're trying to get to the "current day" table which would look like this: TblResult_***** without the _Dayofweek appended?

Mike
"oh, that monkey is going to pay"

Lionheart
Starting Member

41 Posts

Posted - 2008-07-09 : 15:24:57

quote:
Originally posted by mfemenel

Let me ask something to clarify. So you have any number of tables that start off with tblResult_ and then the analysis name and each day the old one is appended with the day of the week like _MON when the new one is created? And you're trying to get to the "current day" table which would look like this: TblResult_***** without the _Dayofweek appended?

Mike
"oh, that monkey is going to pay"

Yes, that is correct.

Thanks

mfemenel
Professor Frink

1421 Posts

Posted - 2008-07-09 : 15:32:06

Well your code in your original post is only going to return tables with 15 chars in the name so you're on the right track. I'm back to my original thought then if you advanced your code a step further and joined to sysobjects by table name and ordered by the create date you'd be there.

select top 1 @DataTable= table_name
from DBSERVER.Results_Table.INFORMATION_SCHEMA.Tables t
inner join sysobjects o on t.table_name=o.name
where TABLE_TYPE = 'BASE TABLE' and len(table_name)=15
order by o.crdate desc

Mike
"oh, that monkey is going to pay"

Lionheart
Starting Member

41 Posts

Posted - 2008-07-09 : 15:35:54

That is great, thanks so much.

mfemenel
Professor Frink

1421 Posts

Posted - 2008-07-09 : 16:06:52

One other thought for you. Just in case there are other tables in your database that happen to be 15 characters you might also want to add a where tablename like 'tblResult_%' just to make sure you get the ones you intended to get.

Mike
"oh, that monkey is going to pay"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources