CODEWITHSUNDEEP
sqlsql-serverinserttemp-tables
faizanjehangir
faizanjehangir

Reputation: 2831

Create temp table from provided variable column names

I want to create a temporary table, in which the columns will be those which I provide as parameter, separated by a delimiter.

For example, if the column names are: id, name, address..the respective table should contain the same amount and header names of the columns. Similarly, next time the column number and names could vary.

Any help in this regard?

Upvotes: 1

Views: 18142

Answers (2)

Eddie LeGault
Eddie LeGault

Reputation: 1

Could you not build a table out of a distinct list from wherever these "Dynamic Field Names" live... Then push that in as a string list... Like... I built a table with colors then got a field of names and now am going to push it into a string that can be used to build out the table headers... no limit to quantity...

SELECT @Fields = coalesce(@Fields + ',', '') +  convert(varchar(50),[name])
                FROM #TempCols
                WHERE column_id > 1
                ORDER BY column_id

Where Column_ID is just a Windowed ROW_Number...

I don't agree with the notion of its not possible ever. There is always a way, we may not see it now but there is always a method that can be nested or abused to bend any rule to what we need.

Upvotes: 0

praveen
praveen

Reputation: 12271

Try this :-

CREATE PROCEDURE GenerateTempTable 
@tableName as nvarchar(max),
@Col1 as nvarchar(255),
@Col2 as nvarchar(255)
AS
BEGIN
Declare @sql nvarchar(max)

set @sql='CREATE TABLE #'+ @tableName  + '
         ('+ @col1+ ' nvarchar(255),'+
          @col2 + ' nvarchar(255)
         )'

-- Select @sql   Check the DDL 

EXECUTE sp_executesql @sql,
N'@tableName nvarchar(max),@Col1 nvarchar(255),@Col2 nvarchar(255)',
@tableName = @tableName,@Col1=@Col1,@Col2=@Col2 


END

The problem with the above query is temp table is created with the dynamic block query therefore it cannot be accessed after the block . In order to access the table outside the scope then you need to create global temp table ##

Edit :- An example with Global Temp Tables and static table name 

ALTER PROCEDURE GenerateTable 
@Col1 as nvarchar(255),
@Col2 as nvarchar(255)
AS
BEGIN
Declare @sql nvarchar(max)

If object_id('tempdb..##TempTable') is not null 
Drop table ##TempTable

set @sql='CREATE TABLE ##TempTable 
        ('+ @col1+ ' nvarchar(255),'+
        @col2 + ' nvarchar(255)
        )'

-- Select @sql   Check the DDL 

 EXECUTE sp_executesql @sql,
 N'@Col1 nvarchar(255),@Col2 nvarchar(255)',
 @Col1=@Col1,@Col2=@Col2 

 END

To execute the SP the sql is :-

 Declare @tableName varchar(max),
 @Col1 varchar(70),
 @Col2 varchar(70)

 Exec GenerateTable @col1='ColA',@Col2='ColB'

Edit 2:-

If you are sure that the number of parameters wont exceed x values ( Say 5) .Then you can create 5 default parameter .Check this link for further details.

Upvotes: 3

Related Questions