RE:SQL对EXCEL读写简单操作[载]

1、向Excel查询
select * from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=yes;database= c:\book1.xls;','select * from [Sheet1$]') where c like '%f%'
select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0' ,'Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:\book1.xls',[sheet1$])
1)hdr=yes时可以把xls的第1行作为字段看待，如第1个中hdr=no的话，where时就会报错
2)[]和美圆$必须要，否则M$可不认这个账

2、修改Execl
update OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=c:\book1.xls;', 'select * from [Sheet1$]') set a='erquan' where c like '%f%'

3、导入导出
insert into OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=c:\book1.xls;', 'select * from [Sheet2$]')(id,name) select id,name from serv_user
或BCP
master..xp_cmdshell'bcp "serv-htjs.dbo.serv_user" out "c:\book2.xls" -c -q -S"." -U"sa" -P"sa"'
从Excel向SQLServer导入：
select * into serv_user_bak from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=yes; database=c:\book1.xls;','select * from [Sheet1$]')
如果表serv_user_bak不存在，则创建