Create a table dynamically in SQL Server

Question

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.

• ypercubeᵀᴹ: I think you need exec (@DynamicSQL); Otherwise SQL-Server tries to find a stored procedure with the name 'create table ...'
• David Spillett: 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.

Answer 1

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);

• code chimp: No luck. I also changed it as - SET @tableToDump = 'backupCdc'
• mouliin: Print your Dynamic SQL insstead of executing and try to find out whats going wrong as it worked on my machine.