drop table #temp



select name,IDENTITY(int,1,1) as id

into #temp

from syscolumns

where id =(

select id from sysobjects 

where xtype='u' and name = 'sheet')

and name not in ('F1')


declare @i int,@totalcount int

set @i=(select max(id) from #temp)

select * from #temp

declare @sql varchar(4000)

declare @s_tablename varchar(20)

declare @s_columnsnname varchar(20)


create table test_20170822(f1 varchar(200),f2 float)


while @i<@totalcount 

begin

  

  set @s_columnsnname=(select  name from #temp where id =@i)

  set @sql= 'insert into test_20170822(f1,f2)

  select f1,'+@s_columnsnname+' from sheet'

  exec sp_executesql @sql    

  set @i=@i+1

end