Help with variable table name in T-SQL
-
I am trying to build a generic T-SQL that I can use to create work tables used for short projects. I am a programmer, not a DBA so please excuse some of my ignorance of T-SQL.
Here is my little script:
use sworks
go
DECLARE @temporary varchar(32)
SET @temporary = 'tq_old_lib_ids_counter'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[@temporary]') AND type in (N'U'))
DROP TABLE [dbo].[@temporary]
go
--SELECT INTO a new, non-temporary table
SELECT TOP (100) PERCENT LIBID, COUNT(*) AS count
INTO [dbo].[@temporary]
FROM dbo.tq_old_lib_ids
GROUP BY LIBID
I am trying to create a table with the name tq_old_lib_ids_counter but the table created keeps coming out as @temporary. I need this table for a few days so I am not creating a temp table.
What am I doing wrong?
-
First, due to the fact that you need the table for a few days, scrap the ideas of using a table variable and a local temp table which will go out of scope and disappear as soon as you close the connection you used to create them. Instead, create either an actual table, or a global temp table which is not dropped until you explicity drop it. Due to the fact that you want the table to be non-temporary, just make it a real table. Try this:
SELECT LIBID, COUNT(*) AS IDCountINTO tq_old_lib_ids_counterFROM dbo.tq_old_lib_idsGROUP BY LIBIDThis will create your table with all your data in it.
-
It appears you are not using the variable [@temporary] properly in it's different contexts. When you are referencing it inside of a quoted string, it needs to be concatenated. Consider the following:
WRONG:
OBJECT_ID(N'[dbo].[@temporary]')
RIGHT:
OBJECT_ID(N'[dbo].[' + @temporary + ']')
In addition, you cannot use a declared variable in a SQL string assuming the parser will translate it first. If you say:
SELECT * FROM @temporary
the parser will think there is a table named @temporary instead of realizing @temporary is just a variable for the real table name.
In this case, you should be able to substitute the following for your insert statement:
EXEC('INTO [dbo].[' + @temporary + '] SELECT TOP (100) PERCENT LIBID, COUNT(*) AS count FROM dbo.tq_old_lib_ids GROUP BY LIBID');
-
Thanks. I tried that using the following:
use sworks
go
DECLARE @temporary varchar(32)
SET @temporary = 'tq_old_lib_ids_counter'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[' + @temporary + ']') AND type in (N'U'))
DROP TABLE [dbo].[' + @temporary + ']
go
EXEC('INTO [dbo].[' + @temporary + '] SELECT TOP (100) PERCENT LIBID, COUNT(*) AS count FROM dbo.tq_old_lib_ids GROUP BY LIBID');
But I am getting an error I don't understand:
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@temporary".
I did declare it !
-
Oops, missed the first line in your request :blink:
In any case, you have a "go" in the middle of your script. This changes your scope and causes your variable to no longer be declared. Just get rid of the Go.
-
Okay. Now it reads:
use sworks
go
DECLARE @temporary varchar(32)
SET @temporary = 'tq_old_lib_ids_counter'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[' + @temporary + ']') AND type in (N'U'))
DROP TABLE [dbo].[' + @temporary + ']
EXEC('INTO [dbo].[' + @temporary + '] SELECT TOP (100) PERCENT LIBID, COUNT(*) AS count FROM dbo.tq_old_lib_ids GROUP BY LIBID');
But I am still getting an error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'INTO'.
-
looks like you've just got to change the last step to:
EXEC('SELECT TOP (100) PERCENT LIBID, COUNT(*) AS count INTO [dbo].[' + @temporary + '] FROM dbo.tq_old_lib_ids GROUP BY LIBID');the INTO clause should be after your SELECT clause and before the FROM clause.
-
That worked! Thank you all very much. I'm off and running now.
-
Oops. I spoke too soon. Boy, this is complicated. The synatx works for creating the table but not for checking its existence and deleting it. Here is the script:
use sworks
go
DECLARE @temporary varchar(32)
SET @temporary = 'tq_old_lib_ids_counter'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[' + @temporary + ']') AND type in (N'U'))
DROP TABLE [dbo].[' + @temporary + ']
EXEC('SELECT TOP (100) PERCENT LIBID, COUNT(*) AS count INTO [dbo].[' + @temporary + '] FROM dbo.tq_old_lib_ids GROUP BY LIBID');
First pass is fine and the table gets created with the literal name. But the second pass is not using the literal. I get:
Msg 3701, Level 11, State 5, Line 5
Cannot drop the table 'dbo.' + @temporary + '', because it does not exist or you do not have permission.
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'tq_old_lib_ids_counter' in the database.
-
That's because your DROP statement looks like this:
DROP TABLE [dbo].[' + @temporary + ']
It should look more like the following if you are using the @temporary variable:
EXEC('DROP TABLE [dbo].[' + @temporary + ']');
Remember that you cannot use a variable in 'in-line' SQL to represent something. In order to do that you must use something like the EXEC function in order to evaluate any variables.
-
I did know that EXEC was the only way to evaluate the variable inline. Thanks. Now my complete scripts works as I wanted it to.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply