How开发者_运维问答 can i add new column in a temporary table based on the query result. Consider the code below
declare @ctr int
set @ctr = 1
while (@ctr <= (select count(*) from #temp2)
begin
alter table #temp
add (select t2.Value from #temp2 t2 where t2.SysID = @ctr) (select t2.Type from #temp3 t3 where t3.SysID = @ctr)
end
something like that. I have some syntax error about this but as far as i know my syntax is correct
you'll have to use dynamic SQL. This is something that I have done before:
SET @sql = 'ALTER TABLE ' + @TableName +
'ADD ' + CONVERT(VARCHAR(100), @ColName) + ' ' +
CONVERT(VARCHAR(100), @TypeName) +
CASE WHEN @TypeName IN ('int', 'datetime', 'money', 'uniqueidentifier', 'bit')
THEN '' ELSE '(' + CONVERT(VARCHAR(10), @Prec) + ')' END + ' NULL'
EXEC(@sql)
this probably doesn't cover every possible datatype but for my requirements, it was enough. In any case, a good place to start
To the best of my knowledge, I don't think you can do this. Your best hope might be to make a global temporary table (double hash ## instead of single hash #), and then sp_executesql on your global temp table to dynamically add the columns.
精彩评论