Create a table dynamically in SQL Server 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.
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);