SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Trying to create a table Dynamically
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kid_on_the_block
Posting Yak Master

172 Posts

Posted - 12/10/2012 :  10:00:53  Show Profile  Reply with Quote
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

United Kingdom
226 Posts

Posted - 12/10/2012 :  10:09:55  Show Profile  Visit theboyholty's Homepage  Reply with Quote
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
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/10/2012 :  10:11:01  Show Profile  Reply with Quote
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
Go to Top of Page

theboyholty
Posting Yak Master

United Kingdom
226 Posts

Posted - 12/10/2012 :  10:12:15  Show Profile  Visit theboyholty's Homepage  Reply with Quote
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
Go to Top of Page

kid_on_the_block
Posting Yak Master

172 Posts

Posted - 12/10/2012 :  10:56:44  Show Profile  Reply with Quote
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)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000