Trying to create a table Dynamically

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trying to create a table Dynamically

Author

Topic

kid_on_the_block
Posting Yak Master

172 Posts

Posted - 2012-12-10 : 10:00:53

Hi

I am trying to create a table dynamically in SQL Server & its failing . Can someone please help me resolve the issue

DECLARE @MyTableName nvarchar(20);
DECLARE @DynamicSQL nvarchar(2000);

SET @MyTableName = convert(varchar,GETDATE(),112)

SET @DynamicSQL =
'CREATE TABLE ' + @MyTableName + ' (
[RowNumber] [int] IDENTITY(0,1) NOT NULL,
[EventClass] [int] NULL,
[Duration] [bigint] NULL,
[SPID] [int] NULL,
[LoginName] [nvarchar](128) NULL,
[NTUserName] [nvarchar](128) NULL,
[HostName] [nvarchar](128) NULL,
[TextData] [ntext] NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[BinaryData] [image] NULL,
[ApplicationName] [nvarchar](128) NULL,
[DatabaseName] [nvarchar](128) NULL,
[ObjectName] [nvarchar](128) NULL,
[Error] [int] NULL
)

'

Exec @DynamicSQL

I am getting the following error :

' is not a valid identifier.

theboyholty
Posting Yak Master

226 Posts

Posted - 2012-12-10 : 10:09:55

Two problems with this:

You're trying to call your table 20121012. You can't begin a table name with a number, needs to be characters at the start of the table name.

Also this: Exec @DynamicSQL should be Exec (@DynamicSQL).

Give that a go and see what happens.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-10 : 10:11:01

quote:
Originally posted by kid_on_the_block

Hi

I am trying to create a table dynamically in SQL Server & its failing . Can someone please help me resolve the issue

DECLARE @MyTableName nvarchar(20);
DECLARE @DynamicSQL nvarchar(2000);

SET @MyTableName = convert(varchar,GETDATE(),112) + 'Table'

SET @DynamicSQL =
'CREATE TABLE ' + @MyTableName + ' (
[RowNumber] [int] IDENTITY(0,1) NOT NULL,
[EventClass] [int] NULL,
[Duration] [bigint] NULL,
[SPID] [int] NULL,
[LoginName] [nvarchar](128) NULL,
[NTUserName] [nvarchar](128) NULL,
[HostName] [nvarchar](128) NULL,
[TextData] [ntext] NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[BinaryData] [image] NULL,
[ApplicationName] [nvarchar](128) NULL,
[DatabaseName] [nvarchar](128) NULL,
[ObjectName] [nvarchar](128) NULL,
[Error] [int] NULL
)'

Exec (@DynamicSQL)

I am getting the following error :

' is not a valid identifier.

Do this

theboyholty
Posting Yak Master

226 Posts

Posted - 2012-12-10 : 10:12:15

Actually you CAN call a table 20121210, but you'd need to put in in square brackets. Sorry, my bad.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum

kid_on_the_block
Posting Yak Master

172 Posts

Posted - 2012-12-10 : 10:56:44

Thanks a ton, It worked I modified my script to add [ ] for the table name & the () for Dynamic SQL

DECLARE @MyTableName nvarchar(20);
DECLARE @DynamicSQL nvarchar(2000);

SET @MyTableName = convert(varchar,GETDATE(),112)

SET @DynamicSQL =
'CREATE TABLE [' + @MyTableName + '] (
[RowNumber] [int] IDENTITY(0,1) NOT NULL,
[EventClass] [int] NULL,
[Duration] [bigint] NULL,
[SPID] [int] NULL,
[LoginName] [nvarchar](128) NULL,
[NTUserName] [nvarchar](128) NULL,
[HostName] [nvarchar](128) NULL,
[TextData] [ntext] NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[BinaryData] [image] NULL,
[ApplicationName] [nvarchar](128) NULL,
[DatabaseName] [nvarchar](128) NULL,
[ObjectName] [nvarchar](128) NULL,
[Error] [int] NULL
)

'

Exec (@DynamicSQL)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources