以文本方式查看主题 - 智睿软件_技术交流论坛 (http://zhirui.net/bbs/index.asp) -- 服务器技术 (http://zhirui.net/bbs/list.asp?boardid=34) ---- sql server 2005与excel互相导入导出 (http://zhirui.net/bbs/dispbbs.asp?boardid=34&id=3439) |
-- 作者:zhirui -- 发布时间:2014-10-28 15:52:16 -- sql server 2005与excel互相导入导出 里查询Excel数据: -- ====================================================== SELECT * FROM OpenDataSource( \'Microsoft.Jet.OLEDB.4.0\', \'Data Source="c:\\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0\')...[Sheet1$]
SELECT * FROM OpenDataSource ( \'Microsoft.Jet.OLEDB.4.0\',
SELECT * into newtable FROM OpenDataSource( \'Microsoft.Jet.OLEDB.4.0\',
SELECT * into newtable FROM OpenDataSource( \'Microsoft.Jet.OLEDB.4.0\',
EXEC master..xp_cmdshell \'bcp 库名.dbo.表名out c:\\Temp.xls -c -q -S"servername" -U"sa" -P""\'
EXEC master..xp_cmdshell \'bcp saletesttmp.dbo.CusAccount out c:\\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"\' EXEC master..xp_cmdshell \'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\\ authors.xls -c -Sservername -Usa -Ppassword\'
insert into OpenDataSource( \'Microsoft.Jet.OLEDB.4.0\', \'Data Source="c:\\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0\')...table1 (A1,A2,A3) values (1,2,3)
INSERT INTO OPENDATASOURCE(\'Microsoft.JET.OLEDB.4.0\', \'Extended Properties=Excel 8.0;Data source=C:\\training\\inventur.xls\')...[Filiale1$] (bestand, produkt) VALUES (20, \'Test\') insert into openrowset(\'Microsoft.Jet.OLEDB.4.0\',\'EXCEL 8.0;HDR=YES;User id=admin;Password=;IMEX=1; DATABASE=D:\\Roy.xls\', sheet1$)--(ID,Name) select 2,\'b\' insert into OPENROWSET(\'MICROSOFT.JET.OLEDB.4.0\',\'Excel 8.0;HDR=YES;DATABASE=D:\\Roy.xls\',sheet1$)(ID,Name) select 2,\'b\' |