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.
Answer by Håkan Winther ·
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
Answer by Melvyn Harbour 1 ·
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!
Answer by Kev Riley ·
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)