Conditionally INSERTING columns into Table

Question

using SQL Azure and like to insert a data to a table using dynamic Query.

No of columns in select statment is dynamic. It will be based on some conditions.

Ex: Salary, Code.

How can I create a table for conditional based columns and do insert in coditions?

I don't want to use SELECT INTO #tempTable as it is not supporting in SQL Azure

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

Answer 1

check the code below

use DBName

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

declare @columns varchar(max)
Declare @colstable table(column_name varchar(200));

SET @sqlFrom = ' FROM dbo.EMPLOYEE AS EMP'
set @sqlSelect = 'INSERT INTO yourtable SELECT '

this code below will get list of column names of your source table

insert into @colstable
SELECT c.name AS column_name
FROM sys.tables AS t 
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
and t.name = 'Source Table name'

this is the main part where you decide which columns you need

Set @columns = ''

select @columns = @columns + case when (**your condition**) then c.column_name + ', ' else '' end
FROM @colstable c

Select @columns = SUBSTRING(@columns,1,LEN(@columns) -1) -- this will just remove the last comma

finally execute

SET @sql =  @sqlSelect + @columns + @sqlFrom 
--select @sql -- just view your generated code before executing it

EXEC sp_executesql @sql