MSSQL批量导入EXCEL

        对于导入含有多个sheet的EXCEL文件一直找不到合适的方法来做，尤其是多服务器的操作更是郁闷。首先应该排除复制的方法，每次所导入的表就不固定。

        我试过2种方法，一种是备份恢复，再用insert语句自动执行。新建立一个数据库，一次性导入EXCEL，备份拷贝到各个服务器，恢复过来，再用游标从这个数据库中insert到正式数据库的对应表内，如果只有一个数据库，或者各个数据库间不存在相同的表名，再忽略掉各服务器语言兼容的问题，这个方法还是可行。

        另外一种是直接拷贝EXCEL文件到分服务器，用opendatasouce/openrowset来导入数据。这个最关键的是能用SQL语句取到EXCEL的各个sheet，在网上搜到一个函数可以解决。

create function [dbo].[udf_getExcelTableNames] (@filename varchar (1000 ))
returns @t table (id int , name varchar (255 ))
as
begin
declare
@error int , @obj int , @c int , @sheetname varchar (255 ) , @sheetstring varchar (255 )

exec @error = sp_oacreate 'Excel.Application' , @obj out
exec @error = sp_oamethod @obj , 'Workbooks.Open' , @c out , @filename
exec @error = sp_oagetproperty @obj , 'ActiveWorkbook.Sheets.Count' , @c out
while (@c > 0 )
begin
set @sheetstring = 'ActiveWorkbook.Sheets(' + ltrim (@c )+ ').Name'
exec @error = sp_oagetproperty @obj , @sheetstring , @sheetname out
insert into @t select @c , @sheetname
set @c = @c - 1
end
exec @error = sp_oadestroy @obj
return
end

再用以下语句可以得到导入脚本：

use database--需要导入的数据库名
set nocount on
declare @filename varchar(200)
set @filename='d:\temp\tt.xls'--EXCEL位置
declare @lenth int
declare @column varchar(1000)--列名
declare @columnnew varchar(1000)--转化列名
declare @tablename varchar(100)--表名
declare @sql varchar(1000)--字符串
declare @temp_error table(tablename varchar(200),errormassage varchar(200))--记录错误
declare mycursor cursor for select name from master.dbo.udf_getExcelTableNames (@filename) order by id
open mycursor                 
fetch next from mycursor into @tablename 
while(@@fetch_status=0)
begin
if not exists(select * from sys.objects where name=@tablename and type='U')
begin
 insert into @temp_error
 select @tablename,'the table not exist'
 goto tt
end
else print '--'+@tablename--显示表名
set @column=''
set @columnnew=''
declare @corID varchar(100)
declare cursor_column cursor for select name from dbo.syscolumns where id=object_id(@tablename)
open cursor_column   
fetch next from cursor_column into @corID
while(@@fetch_status=0)
begin
    if exists(select * from  syscolumns where id=object_id(@tablename) and name=@corID
    and xusertype in (35,99,167,175,231,239))--转化字符
    begin
    select @lenth=prec from syscolumns where id=object_id(@tablename) and name=@corID
    select @column=@column+',cast('+@corID+'as varchar('+cast(@lenth as varchar(6))+'))'
    end
    else select @column=@column+','+@corID
    select @columnnew=@columnnew+','+@corID
fetch next from cursor_column into @corID
end
close cursor_column
deallocate cursor_column
set @column=substring(@column,2,len(@column))
set @columnnew=substring(@columnnew,2,len(@columnnew))
    --如果是标识列，则屏蔽掉再插入数据
if exists(select colstat from dbo.syscolumns where colstat=1 and id=object_id(@tablename))
begin
   set @sql='
   SET IDENTITY_INSERT'+@tablename+'on
   delete from'+@tablename+'
   insert intodbo.'+@tablename+'('+@columnnew+')
   SELECT'+@column+'
   FROM OpenDataSource(''MICROSOFT.JET.OLEDB.4.0'', ''Excel 8.0; HDR=No; IMEX=0;
   DATABASE='+@filename+''')...['+@tablename+'$]
   SET IDENTITY_INSERT'+@tablename+'off'
   print(@sql)
end
    --没有标识列则直接插入
if not exists(select colstat from syscolumns where colstat=1 and id=object_id(@tablename))
begin
   set @sql='
   delete from'+@tablename+'
   insert intodbo.'+@tablename+'('+@columnnew+')
   SELECT'+@column+'
   FROM OpenDataSource(''MICROSOFT.JET.OLEDB.4.0'', ''Excel 8.0; HDR=No; IMEX=0;
   DATABASE='+@filename+''')...['+@tablename+'$]'
   print(@sql)
end
tt:     
  fetch next from mycursor into @tablename
end
close mycursor
deallocate mycursor
select * from @temp_error

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21383461/viewspace-623856/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21383461/viewspace-623856/