通过SQL语句直接实现Excel与数据库的导入导出

 约 46 分钟

1、在SQL数据库中直接从Excel里面查询数据:

select \* from  
    OPENROWSET('MICROSOFT.JET.OLEDB.4.0'  
    ,'Excel 5.0;HDR=YES;DATABASE=c:\\test.xls',sheet1$) 

2、从Excel文件中,导入数据到SQL数据库中,
  

 select \* into 表 from   
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) OPENROWSET('MICROSOFT.JET.OLEDB.4.0'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) ,'Excel 5.0;HDR=YES;DATABASE=c:\\test.xls',sheet1$)  

3、从SQL数据库中,导出数据到Excel(excel存在),

   insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) ,'Excel 5.0;HDR=YES;DATABASE=c:\\test.xls',sheet1$)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) select \* from 表  

4、从SQL数据库中,导出数据到Excel(excel不存在),
   

 \---- 导出表  
    EXEC master..xp\_cmdshell 'bcp 数据库名.dbo.表名 out "c: est.xls" /c -/S"服务器名" /U"用户名" -P"密码"'  
    ---- 导出查询语句  
    EXEC master..xp\_cmdshell 'bcp "SELECT au\_fname, au\_lname FROM pubs..authors ORDER BY au\_lname" queryout "c: est.xls" /c -/S"服务器名" /U"用户名" -P"密码"'  

5、导入导出的存储过程

![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)\--下面是导出真正Excel文件的方法:(请将一下所有代码复制到存储过程中)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)ifexists (select\*from dbo.sysobjects where id \=object\_id(N'\[dbo\].\[p\_exporttb\]') andOBJECTPROPERTY(id, N'IsProcedure') \=1)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)dropprocedure\[dbo\].\[p\_exporttb\]  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)GO  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedBlockStart.gif)/\*\--数据导出EXCEL  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif)   
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif) 导出表中的数据到Excel,包含字段名,文件为真正的Excel文件  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif) ,如果文件不存在,将自动创建文件  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif) ,如果表不存在,将自动创建表  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif) 基于通用性考虑,仅支持导出标准数据类型  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedBlockEnd.gif)\--邹建 2003.10(引用请保留此信息)--\*/  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedBlockStart.gif)/\*\--调用示例  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif) p\_exporttb @tbname='地区资料',@path='c:',@fname='aa.xls'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedBlockEnd.gif)\--\*/  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)createproc p\_exporttb  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)@tbname sysname,    \--要导出的表名  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)@pathnvarchar(1000),   \--文件存放目录  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)@fnamenvarchar(250)\=''\--文件名,默认为表名  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)as  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)declare@errint,@srcnvarchar(255),@descnvarchar(255),@outint  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)declare@objint,@constrnvarchar(1000),@sqlvarchar(8000),@fdlistvarchar(8000)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)\--参数检测  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)ifisnull(@fname,'')\=''set@fname\=@tbname+'.xls'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)\--检查文件是否已经存在  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)ifright(@path,1)<>''set@path\=@path+''  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)createtable #tb(a bit,b bit,c bit)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)set@sql\=@path+@fname  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)insertinto #tb exec master..xp\_fileexist @sql  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)\--数据库创建语句  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)set@sql\=@path+@fname  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)ifexists(select1from #tb where a\=1)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) set@constr\='DRIVER={Microsoft Excel Driver (\*.xls)};DSN='''';READONLY=FALSE'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)       +';CREATE\_DB="'+@sql+'";DBQ='+@sql  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)else  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) set@constr\='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)    +';DATABASE='+@sql+'"'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)\--连接数据库  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)exec@err\=sp\_oacreate 'adodb.connection',@obj out  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)if@err<>0goto lberr  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)exec@err\=sp\_oamethod @obj,'open',null,@constr  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)if@err<>0goto lberr  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedBlockStart.gif)/\*\--如果覆盖已经存在的表,就加上下面的语句  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif)\--创建之前先删除表/如果存在的话  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif)select @sql='drop table \['+@tbname+'\]'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif)exec @err=sp\_oamethod @obj,'execute',@out out,@sql  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedBlockEnd.gif)\--\*/  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)\--创建表的SQL  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)select@sql\='',@fdlist\=''  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)select@fdlist\=@fdlist+',\['+a.name+'\]'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) ,@sql\=@sql+',\['+a.name+'\] '  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  +case   
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)   when b.name like'%char'   
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)   thencasewhen a.length\>255then'memo'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)    else'text('+cast(a.length asvarchar)+')'end  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)   when b.name like'%int'or b.name\='bit'then'int'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)   when b.name like'%datetime'then'datetime'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)   when b.name like'%money'then'money'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)   when b.name like'%text'then'memo'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)   else b.name end  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)FROM syscolumns a leftjoin systypes b on a.xtype\=b.xusertype  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)where b.name notin('image','uniqueidentifier','sql\_variant','varbinary','binary','timestamp')  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) andobject\_id(@tbname)\=id  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)select@sql\='create table \['+@tbname  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) +'\]('+substring(@sql,2,8000)+')'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) ,@fdlist\=substring(@fdlist,2,8000)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)exec@err\=sp\_oamethod @obj,'execute',@out out,@sql  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)if@err<>0goto lberr  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)exec@err\=sp\_oadestroy @obj  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)\--导入数据  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)set@sql\='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES;IMEX=1  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)   ;DATABASE='+@path+@fname+''',\['+@tbname+'$\])'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from '+@tbname)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)return  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)lberr:  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) exec sp\_oageterrorinfo 0,@src out,@desc out  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)lbexit:  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) selectcast(@errasvarbinary(4)) as 错误号  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  ,@srcas 错误源,@descas 错误描述  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) select@sql,@constr,@fdlist  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)go  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)   
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)ifexists (select\*from dbo.sysobjects where id \=object\_id(N'\[dbo\].\[p\_exporttb\]') andOBJECTPROPERTY(id, N'IsProcedure') \=1)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)dropprocedure\[dbo\].\[p\_exporttb\]  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)GO  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedBlockStart.gif)/\*\--数据导出EXCEL  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif)   
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif) 导出查询中的数据到Excel,包含字段名,文件为真正的Excel文件  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif) 如果文件不存在,将自动创建文件  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif) 如果表不存在,将自动创建表  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif) 基于通用性考虑,仅支持导出标准数据类型  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedBlockEnd.gif)\--邹建 2003.10(引用请保留此信息)--\*/  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedBlockStart.gif)/\*\--调用示例  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif) p\_exporttb @sqlstr='select \* from 地区资料'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif)  ,@path='c:',@fname='aa.xls',@sheetname='地区资料'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedBlockEnd.gif)\--\*/  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)createproc p\_exporttb  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)@sqlstrvarchar(8000),   \--查询语句,如果查询语句中使用了order by ,请加上top 100 percent  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)@pathnvarchar(1000),   \--文件存放目录  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)@fnamenvarchar(250),   \--文件名  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)@sheetnamevarchar(250)\=''\--要创建的工作表名,默认为文件名  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)as   
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)declare@errint,@srcnvarchar(255),@descnvarchar(255),@outint  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)declare@objint,@constrnvarchar(1000),@sqlvarchar(8000),@fdlistvarchar(8000)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)\--参数检测  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)ifisnull(@fname,'')\=''set@fname\='temp.xls'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)ifisnull(@sheetname,'')\=''set@sheetname\=replace(@fname,'.','#')  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)\--检查文件是否已经存在  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)ifright(@path,1)<>''set@path\=@path+''  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)createtable #tb(a bit,b bit,c bit)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)set@sql\=@path+@fname  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)insertinto #tb exec master..xp\_fileexist @sql  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)\--数据库创建语句  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)set@sql\=@path+@fname  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)ifexists(select1from #tb where a\=1)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) set@constr\='DRIVER={Microsoft Excel Driver (\*.xls)};DSN='''';READONLY=FALSE'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)       +';CREATE\_DB="'+@sql+'";DBQ='+@sql  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)else  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) set@constr\='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)    +';DATABASE='+@sql+'"'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)\--连接数据库  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)exec@err\=sp\_oacreate 'adodb.connection',@obj out  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)if@err<>0goto lberr  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)exec@err\=sp\_oamethod @obj,'open',null,@constr  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)if@err<>0goto lberr  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)\--创建表的SQL  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)declare@tbname sysname  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)set@tbname\='##tmp\_'+convert(varchar(38),newid())  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)set@sql\='select \* into \['+@tbname+'\] from('+@sqlstr+') a'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)exec(@sql)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)select@sql\='',@fdlist\=''  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)select@fdlist\=@fdlist+',\['+a.name+'\]'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) ,@sql\=@sql+',\['+a.name+'\] '  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  +case   
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)   when b.name like'%char'   
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)   thencasewhen a.length\>255then'memo'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)    else'text('+cast(a.length asvarchar)+')'end  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)   when b.name like'%int'or b.name\='bit'then'int'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)   when b.name like'%datetime'then'datetime'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)   when b.name like'%money'then'money'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)   when b.name like'%text'then'memo'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)   else b.name end  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)FROM tempdb..syscolumns a leftjoin tempdb..systypes b on a.xtype\=b.xusertype  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)where b.name notin('image','uniqueidentifier','sql\_variant','varbinary','binary','timestamp')  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) and a.id\=(select id from tempdb..sysobjects where name\=@tbname)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)if@@rowcount\=0return  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)select@sql\='create table \['+@sheetname  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) +'\]('+substring(@sql,2,8000)+')'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) ,@fdlist\=substring(@fdlist,2,8000)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)exec@err\=sp\_oamethod @obj,'execute',@out out,@sql  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)if@err<>0goto lberr  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)exec@err\=sp\_oadestroy @obj  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)\--导入数据  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)set@sql\='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)   ;DATABASE='+@path+@fname+''',\['+@sheetname+'$\])'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from \['+@tbname+'\]')  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)set@sql\='drop table \['+@tbname+'\]'  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)exec(@sql)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)return  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)lberr:  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) exec sp\_oageterrorinfo 0,@src out,@desc out  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)lbexit:  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) selectcast(@errasvarbinary(4)) as 错误号  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)  ,@srcas 错误源,@descas 错误描述  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) select@sql,@constr,@fdlist  
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)go
阅读 150

推荐阅读
目录