Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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