汇总的OPENROWSET，OpenDataSource

 

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\account.xls', ' SELECT * FROM  [sheet1$]')

 -- 从Excel取数据
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=c:\account.xls',[Sheet1$])

SELECT a.*
FROM OPENROWSET('SQLOLEDB','tao';'sa';'','SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\account.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$
 

 --ODBC 的OLE DB 提供程序
SELECT a.* FROM table1 a LEFT JOIN OPENROWSET('MSDASQL','DRIVER={SQL Server};SERVER=192.168.0.1,2412;UID=sa;PWD=',DbName.dbo.table2) AS b ON a.id = b.id ORDER BY a.ID DESC
--将地址为192.168.0.1端口为2412的SQL SERVER上的table2表和本地服务器上的table1表联接。

SELECT * FROM （SELECT a.* FROM table1 a UNION SELECT b.* FROM OPENROWSET('MSDASQL','DRIVER={SQL Server};SERVER=192.168.0.1,2412;UID=aa;PWD=bb',DbName.dbo.table2) AS b) TempTable ORDER BY ID DESC
--将地址为192.168.0.1端口为2412的SQL SERVER上的table2表中内容加到table1表中。

--使用SQL Server 的 Microsoft OLE DB:

OPENROWSET('SQLOLEDB','服务器地址,端口';'用户名';'密码', 'SQL语句')

--使用Jet 的 Microsoft OLE DB:
OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\test.mdb';'admin';'pwd', Orders)

--注意：以上两种用户名和密码前都是用分号分隔。
select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\test.mdb';'admin';'', Orders)
select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\test.mdb';'admin';'','select * from  Orders')
select * from OpenDataSource('Microsoft.Jet.oledb.4.0','c:\Test.mdb;UserID=admin;Password=;Extended properties=Access')...Orders

insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$ (A1,A2)   values (1,2)
--注：A1,A2,A3 为自己在Excel文档中添加的列名，而不是Excel 默认的 A,B,C,D等

insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$  SELECT  ID FROM CUSTOMER

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/lwl0606/archive/2007/04/23/1575494.aspx