-1

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
0

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.