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
原文：http://xbh1314.blog.51cto.com/4014284/1958585