Printable View
Hello,
I am trying to write a query to pull data from a table that is system generated daily at midnight. I tried:
declare @tablename varchar(50) set @tablename = (select name from sysobjects where crdate = '05/01/2003') select * from @tablename
But I get an error on 'select * from @tablename' (must declare @tablename)
Any assistance is GREATLY APPRECIATED!!!!
(1)
According to your original post.It seems you only create one table per day.
Because if you have more than one table.You will get a error (subquery cant have multiple returns)
----------------------------------------
(2)here is the modified query
Instead of 50 ,put 128 instead!
declare @tablename varchar(128) set @tablename = (
select name from sysobjects where left(crdate,10) = left(convert(datetime,'11/13/1998'),10)
)
---------------------------------------
(3)You can run this to see the result first.If you have many table name returns,you have to put the result into a table and use loop or cursor to retrive row by row
select name from sysobjects where left(crdate,10) =
left(convert(datetime,'11/13/1998'),10)
if it is only one table
------------------------
declare @tablename varchar(128)
set @tablename = (select top 1 name from sysobjects where convert(varchar(8),crdate,112) = '20030501' and type ='u')
exec ('select * from '+ @tablename )