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.
Best Answer
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
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!
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)
