Unanswered: Using Variable as table name in SELECT statement
Good day all,
trying to use variables to hold two pieces of information.
The first being the SQL server name, and the second which is a table name, pulled from another table via a loop.
these two variables are then strung together and held in a third variable which is then used in a select query.
Tested the variables and it prints the correct string and loops perfectly, however the SELECt part does not like calling
the table from a variable. Hard-coded it all works well but using the variable it falls over.
I'm hoping my syntax is wrong so it can be corrected but if not then my method of using a variable for a table name in the SELECT statement is flawed and requires a re-think.
The code is as follows, can anyone see why the SELECt statement does not like the variable?
DECLARE @dbName varchar(50)
DECLARE @tblName varchar(50)
DECLARE @DBTbl varchar(50)
set @dbName = 'DatabaseName.DBO.'
set @tblName = ''
While @tblName is not null
select @tblName = min(MyTable)
WHERE MyField > @tblName
set @DBTbl = @dbName + @tblName
This part works fine, if I add END, GO, it prints the list of tablenames in full.
This point here it falls over...
if @tblName is not null
Insert INTO dbo.tblKeepCount
SELECT COUNT(Accounts) AS initCount, convert(varchar, getdate(), 101) AS wDate
The error message I get is:-
Must declare the variable '@DBTbl'.
So is it not possible to pull a table from a variable?