Using a query to form part of the table name

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 2000 Forums

Transact-SQL (2000)

Using a query to form part of the table name

Author

Topic

Axxib
Starting Member

6 Posts

Posted - 2009-10-07 : 13:09:17

Hi

I need to use a query to form part of the table name in a select statement, something like:

select * from [Transactions_M(select Month_No from Month_Counter)]

(which obviously does not work)

so if the result of the sub query:
"select Month_No from Month_Counter"
is 25, then the end query should be
"select * from [Transactions_M25]"

Any Ideas would be appreciated.

Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-10-07 : 13:21:47

You need dynamic SQL for that...try this...

declare @sql varchar(500)
select @sql = Month_No from Month_Counter
set @sql = 'select * from [Transactions_M' + @sql + ']'
exec(@sql)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-08 : 04:35:26

declare @sql varchar(500),@month_no int
SELECT @month_no=Month_No from Month_Counter
select @sql = 'select * from [Transactions_M' + cast(@month_no as varchar(10))+ ']'
exec(@sql)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources