create table in a script t sql !?

Question

create table in a script t sql !?

christophe.bernard 47659

Ten Centuries

Points: 1169
More actions
June 9, 2011 at 10:42 am

#389697

Hi,
I'm a beginner with t sql in sql server 2005.
I've try to create a simple script who creates some table but i have a problem !!!
here is it my code :
declare @t varchar(500)
set @t = @tblApplication + @PERS_SITE => contains some value
CREATE TABLE [dbo].[@t](
[Person_ID] [int] NOT NULL,
[MayLog] [bit] NOT NULL,
[Rubrique] [bit] NOT NULL CONSTRAINT [@constraintName] DEFAULT ((1))
ON [PRIMARY]
but when i execute the script by pressing F5 and refresh the database i see a new table NEW CREATED NAMED => @t !!!!
i can 't understand !!
any idea ?
Thanls for your time
christophe

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

The brackets are telling the database engine that all characters between them are part of the table name. Thus you have @t as your table name instead of your variable.

In order to create a table dynamically as you are looking to do - you would need to use dynamic sql statements to do so.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 2

Something like this:

declare @t varchar(max)

declare @n varchar(50)

set @n = 'MyAppMyTable'

select @t = 'create table [' + @n + '] ( [Person_ID] [int] NOT NULL,

[MayLog] [bit] NOT NULL,

[Rubrique] [bit] NOT NULL CONSTRAINT [@constraintName] DEFAULT ((1))

ON [PRIMARY]'

exec(@t)

christophe.bernard 47659 Ten Centuries Points: 1169 More actions · Answer 3

Hello all,

all right thanks for your time and your sample that's work and i have understand 😉 !

have a nice day

christophe