CREATE TABLE with Dynamic Column

Question

I need to insert some data into a temp table.

I have some conditional based columns like Salary, Code.

How can I create a table for conditional based columns? I don't want to use SELECT INTO #tempTable

Here is the code:

DECLARE @sql NVARCHAR(MAX)
,@sqlSelect NVARCHAR(MAX) = ''
,@sqlFrom NVARCHAR(MAX) =''

CREATE TABLE #myTempTable (Id INT, DeptId INT, DeptName VARCHAR(100))


SET @sqlSelect ='INSERT INTO #myTempTable
SELECT EMP.Id, EMP.DeptId, EMP.DeptName'

SET @sqlFrom =' FROM dbo.EMPLOYEE AS EMP'

IF (someCondition)
BEGIN
    SET @sqlSelect = @sqlSelect +', EMP.Salary, EMP.Code'            
END

SET @sql =  @sqlSelect +@sqlFrom 

EXEC sp_executesql @sql

Any help/suggestion on how better i can make this?

---

Update:

Initially I used SELECT INTO #TempTable without specifying no of columns, As SQL Azure not supporting that, I decided to go with INSERT INTO. But not sure how can I add dynamic columns in a defined structure already. Its fully dynamic SQL :(

Answer 1

How many dynamic columns do you need?

What about having a column called Dynamic of type nvarchar(MAX) or something of the like, and then you can just put your data in there and format as appropriate.

Another option is to create a table with NULL columns.

To do this in SQL, you can do the following:

CREATE TABLE tblPerson
(
    PersonId INT,
    FirstName NVARCHAR(256),
    LastName NVARCHAR(256) NULL,
    PRIMARY KEY (PersonId)
)

Notice the NULL column above also.

See here for further explanation on creating tables with Primary Keys and NULL columns in SQL:

Create tables in SQL

Answer 2

As alluded to, your best bet is to likely create all of the columns that you will need and make them NULLable if optional. So:

CREATE TABLE #myTempTable (Id INT, DeptId INT, DeptName VARCHAR(100), Salary INT NULL, EmpCode VARCHAR(45) NULL);

And

IF (someCondition)
BEGIN
    SET @sqlSelect = @sqlSelect +', EMP.Salary, EMP.Code'            
END
ELSE
BEGIN
    SET @sqlSelect = @sqlSelect +', NULL, NULL'            
END