question

Amritha avatar image
Amritha asked

Create a table using two columns in a table

I have a temp table that has two columns. One column represents column names. The second column represents data type of the columns.

Now I need to construct a table with these two columns. Any pointers would be appreciated.

Thanks.

sql-server-2008temporary-table
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered

This is a small sample on how you could do it. I am not saying that it is the best or the most beautiful code, but it will do the trick.

CREATE TABLE #tmp
(
    Col1 varchar(20),
    Col2 varchar(20)
)
INSERT INTO #tmp (Col1, Col2) VALUES('Test', 'int'),('Test2','DateTime')

DECLARE @cols as varchar(2000)
DECLARE @sql as nvarchar(4000)

SET @cols=''
SELECT @cols=@cols + '[' + Col1 + ']' + ' ' + col2 +',' FROM #tmp 

SELECT @sql=N'CREATE TABLE yourName (' + LEFT(@cols, len(@cols)-1) +')'
EXEC sp_executeSQL @sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Melvyn Harbour 1 avatar image
Melvyn Harbour 1 answered

You should be able to do it fine using dynamic SQL. Basically, it's quite a similar problem to creating something like a CSV file or similar out of the data, it's just that the bits of text you're using to join your fields together aren't just commas!

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

Another flavour......

declare @temptable table(
    column_name varchar(100),
    datatype varchar(100)
    )

insert into @temptable
select 'col1','int'
union select 'col2', 'varchar(50)'

select * from @temptable

declare @sqlstring varchar(max)

select @sqlstring = 
    'create table newtable ( ' + 
    	stuff ( ( SELECT
    	             ', ' + column_name + ' ' + datatype + ' '
                  FROM  @temptable t1
    	FOR XML PATH ( '' ) ) , 1 , 2 , '' )
    	+ ' )'
from @temptable

select @sqlstring

exec(@sqlstring)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.