0

I am trying to create a table dynamically depending upon current date in SQL Server. But I am unable to do so. I am getting error as invalid identifier.

These are the details:

SQL query:

DECLARE @tableToDump nvarchar(100); 

SET @tableToDump = 'backupCdc'+cast(cast(GETDATE() as date) as char(100));

DECLARE @DynamicSQL nvarchar(1000);

SET @DynamicSQL=N'create table '+ @tableToDump +' ('+'cid int primary key AUTO_INCREMENT, employeeno Varchar(100), fieldName Varchar(100) NOT NULL, fieldValue Varchar(1000))';

exec @DynamicSQL;

Output (error):

The name 'create table backupCdc2015-09-10 (cid int primary key AUTO_INCREMENT, employeeno Varchar(100), fieldName Varchar(100) NOT NULL, fieldValue Varchar(1000))' is not a valid identifier.

  • 1
    I think you need exec (@DynamicSQL); Otherwise SQL-Server tries to find a stored procedure with the name 'create table ...' – ypercubeᵀᴹ Sep 10 '15 at 9:32
  • Yup! :) That's the way to go!! thanks ypercube :) – code chimp Sep 10 '15 at 9:36
  • Be careful to assure yourself that creating many tables like this is a good idea - it is a pattern that can be a pain to maintain and report upon (the book "SQL Anitpatterms" covers it and the problems it can cause in the chapter "metadata tribbles". If you are doing this for performance reasons and have access to Enterprise Edition then consider using the built-in support for table partitioning instead. – David Spillett Sep 10 '15 at 13:08
0

You can try with different format of date. I also changed AUTO_INCREMENT to Identity

DECLARE @tableToDump nvarchar(100); 

SET @tableToDump = 'backupCdc'+CONVERT(varchar(10),getdate(),112);

DECLARE @DynamicSQL nvarchar(1000);

SET @DynamicSQL=N'create table '+ @tableToDump +' ('+'cid int primary key IDENTITY(1,1), employeeno Varchar(100), fieldName Varchar(100) NOT NULL, fieldValue Varchar(1000))';
/*Adding braces is important as suggested by ypercube */
exec (@DynamicSQL);
  • No luck. I also changed it as - SET @tableToDump = 'backupCdc'; – code chimp Sep 10 '15 at 9:29
  • Print your Dynamic SQL insstead of executing and try to find out whats going wrong as it worked on my machine. – mouliin Sep 10 '15 at 9:35
  • any way thanks mouliin, ypercube answer also helped me. Thanks for both – code chimp Sep 10 '15 at 9:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.