CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005create-table
Jayson D. Galarretta
Jayson D. Galarretta

Reputation: 11

Is it possible for me to use a variable in a CREATE TABLE statement?

I would like to do something like the following...

declare @tableName as varchar(100)

set @tableName = 'MyNewTable'

CREATE TABLE @tableName
(
column1 int identity(1,1) PRIMARY KEY,
column2 varchar(50) not null,
column3 datetime
)

...to parameterize the table name in a CREATE TABLE statement; but when I run this as-is, I get the error Incorrect syntax near '@tableName'.

How can I use a variable in my CREATE TABLE statement?

Upvotes: 1

Views: 69

Answers (3)

J0e3gan
J0e3gan

Reputation: 8938

Depending on your needs, you can also use a SQLCMD variable to parameterize a table name in a CREATE TABLE statement - for example:

:setvar tableName "MyNewTable"

CREATE TABLE $(tableName)
(
column1 int identity(1,1) PRIMARY KEY,
column2 varchar(50) not null,
column3 datetime
)

The potential benefit of this approach (depending on your situation) is being able to specify the table name with a sqlcmd command-line argument - i.e. sqlcmd.exe [...] -v tableName="MyNewTable" (in which case you would comment out the :setvar command in the script - since the command-line arg will declare and initialize the expected tableName SQLCMD variable).

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93694

For that you should use dynamic SQL:

DECLARE @tableName AS VARCHAR(100),
        @sql       NVARCHAR(max)

SET @tableName = 'MyNewTable'
SET @sql ='CREATE TABLE ' + @tableName
          + ' ( column1 int identity(1,1) PRIMARY KEY, column2 varchar(50) not null, column3 datetime )'
print @sql
EXEC Sp_executesql
  @sql

Upvotes: 2

radar
radar

Reputation: 13425

You can do it like this without dynamic SQL:

DECLARE @tableName as varchar(100)

SET @tableName = 'MyNewTable'
CREATE TABLE TestTemp ( column1 int identity(1,1) PRIMARY KEY, 
                        column2 varchar(50) not null, 
                        column3 datetime )
EXEC sp_rename TestTemp , @tableName

Upvotes: 2

Related Questions