SQL Server与Excel数据交互：导入导出教程

  1、在sql server里查询excel数据:
 -- ======================================================
 select  
 from opendatasource( ' microsoft.jet.oledb.4.0'  
 ' data source=" c:\book1.xls"  user id=admin password= extended properties=excel 5.0' )...[sheet1$]
 下面是个查询的示例，它通过用于 jet 的 ole db 提供程序查询 excel 电子表格。
 select  
 from opendatasource ( ' microsoft.jet.oledb.4.0'  
   ' data source=" c:\finance\account.xls"  user id=admin password= extended properties=excel 5.0' )...xactions
 -------------------------------------------------------------------------------------------------

 2、将excel的数据导入sql server :
 -- ======================================================
 select  into newtable
 from opendatasource( ' microsoft.jet.oledb.4.0'  
   ' data source=" c:\book1.xls"  user id=admin password= extended properties=excel 5.0' )...[sheet1$]
 实例:
 select  into newtable
 from opendatasource( ' microsoft.jet.oledb.4.0'  
   ' data source=" c:\finance\account.xls"  user id=admin password= extended properties=excel 5.0' )...xactions
 -------------------------------------------------------------------------------------------------

 3、将sql server中查询到的数据导成一个excel文件
 -- ======================================================
 t-sql代码：
   master..xp_cmdshell ' bcp 库名.dbo.表名out c:\temp.xls -c -q -s" servername"  -u" sa"  -p" " ' 
 参数：s 是sql服务器名；u是用户；p是密码
 说明：还可以导出文本文件等多种格式
 实例:  master..xp_cmdshell ' bcp saletesttmp.dbo.cusaccount out c:\temp1.xls -c -q -s" pmserver"  -u" sa"  -p" sa" ' 
   master..xp_cmdshell ' bcp " select au_fname  au_lname from pubs..authors order by au_lname"  queryout c:\ authors.xls -c -sservername -usa -ppassword'