Sql-server – Create a table dynamically in SQL Server

dynamic-sqlsql 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.

Best Answer

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

Related Solutions

Sql-server – Table structure for many custom profiles

It always pains me to suggest it but theres a reasonable case for making use of an XML column here.

Create appropriate tables to handle commonly used fields i.e. name, address, email, age, gender etc. I'd be tempted to store the custom fieldsets as an XML column. Once the number of subscribers they are emailing reaches a level where performance may become a problem, trigger a notification to the DBA so they can review the custom fieldsets created by your users and see if there is justification for moving them out of XML columns to defined sub-type tables.

Obviously you end up with two code paths for handling XML and sub-type definitions but you retain flexibility while having a process for dealing with the potential performance implications.

If SQL2008 was an option, I'd consider making use of sparse columns for the custom fields (generic definitions, along the lines you mentioned) and combine with filtered indexes for the larger customers.

Sql-server – Allow self-referential foreign keys to be null from an alter-table

If there are no illegal values in C.ParentID, then I am not clear what the problem might be. Is this Microsoft SQL Server?

I created a test case as follows:

Create Table dbo.Hier
(Id  INT IDENTITY Primary KEy,
 ParID INT NULL,
 Other varchar(10));
GO
 INSERT INTO dbo.Hier (ParID, Other) VALUES (NULL, 'Able');
 INSERT INTO dbo.Hier (ParID, Other) VALUES (1, 'Baker');
 INSERT INTO dbo.Hier (ParID, Other) VALUES (NULL, 'Charlie');
GO
ALTER TABLE dbo.Hier ADD 
      CONSTRAINT FK_Parent
      FOREIGN    KEY (ParID)
      REFERENCES Hier(ID);
GO
INSERT INTO dbo.Hier (ParID, Other) VALUES (3, 'Delta');
SELECT * FROM dbo.Hier;
GO
DROP TABLE dbo.Hier;
GO

Seems to work fine. If you are on a different SQL Server it might have different behavior. Or fluentMigrator may have a problem.

Best Answer

Related Solutions

Sql-server – Table structure for many custom profiles

Sql-server – Allow self-referential foreign keys to be null from an alter-table

Related Question