Dynamic table naming in a query

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

SQL Server 2012 Forums

Transact-SQL (2012)

Dynamic table naming in a query

Author

Topic

stephenh
Starting Member

3 Posts

Posted - 2014-07-24 : 06:56:13

Hi looking for some advice on how to achieve this

Basically I trying to dynamically select from a table in a stored procedure which changes every month

Example one month its called

dbo.TableName_COR148
the next
dbo.TableName_COR149

not always sequential by the way
There is an index table which stores the names of all the monthly tables dbo.ModelSet

First I declared a variable and tried to use that in the from clause but sql returned an error that a table variable needs to be used but I cant figure out how this would work for me currently as it looks like I can only do a query in a table variable and no dynamic sql??

If I perform something like the following query or in another stored procedure I can return the results of the table I need

use MyDB

declare @sql1 varchar(50)
declare @sql2 varchar(50)
set @sql1='select * from dbo.TableName_'
set @sql2=(select max(model) from dbo.ModelSet)
set @sql1=@sql1+@sql2

print @sql1
--test to return the select statement

exec (@sql1)
--executes the select statement

What is the best way to select from the latest table in a stored procedure so sp is fully automated?

Thank you very much for any pointers

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-07-24 : 07:49:59

This is poor design. You should really have one table with a Model column. Your query would then be:


SELECT *
FROM dbo.TableName
WHERE Model = (SELECT MAX(Model)FROM dbo.ModelSet);

If this is a 3rd party product that you have to cope with, try something like:


DECLARE @sql varchar(8000);

SELECT TOP (1) @sql = 'SELECT * FROM ' + TABLE_SCHEMA + '.' + TABLE_NAME + ';'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
	AND TABLE_NAME LIKE 'TableName_%'
ORDER BY TABLE_NAME DESC

--PRINT @sql;

EXEC (@sql);

stephenh
Starting Member

3 Posts

Posted - 2014-07-24 : 10:13:33

Thanks a lot for the info, yes its a third party database
Each version of the table has millions of rows

Yes I can achieve a select of the table by executing the @sql you provided thanks but how can I use this data somehow in the FROM clause of my stored procedure?

Thanks

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-07-25 : 05:26:50

You will have to provide a lot more information for a specific answer.

Basically if the FROM clause is dynamic then the whole of the query will have to be dynamic.

stephenh
Starting Member

3 Posts

Posted - 2014-07-25 : 09:06:50

OK in its simplest form

I would like to select the latest version of the table on the fly at a given time

So

Select * from

***Possible list of tables

dbo.TableName_COR146
dbo.TableName_COR147
dbo.TableName_COR148
dbo.TableName_COR149
dbo.TableName_COR151 (This is the table I would like to select from)

Thanks

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-07-25 : 12:40:44

quote:
Originally posted by stephenh

OK in its simplest form

I would like to select the latest version of the table on the fly at a given time

So

Select * from

***Possible list of tables

dbo.TableName_COR146
dbo.TableName_COR147
dbo.TableName_COR148
dbo.TableName_COR149
dbo.TableName_COR151 (This is the table I would like to select from)

Thanks

Umm...

So what is wrong with the previous bit of dynamic sql?
Just put it in the SP.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2014-07-25 : 13:34:33

What I would do for this is create a synonym for the latest version of the table - and have my code reference the synonym instead.

Once the new table has been created, drop the synonym and recreate it with the new table name. Something like:

DROP SYNONYM dbo.SynonymName;
CREATE SYNONYM dbo.SynonymName FOR dbo.TableName_COR151;

In my code, I would use:

SELECT ... FROM dbo.SynonymName;

Now I don't have to worry about changing my code, just update the synonym and my code will reference the latest available table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources