OpenRowSet和OpenDataSource的运用技巧

/*===================================================================================================
注:前提条件是SQL Server服务器端必须安装有相应的驱动程序，
 Microsoft.ACE.OLEDB.12.0是Office2007中Excel和Access的驱动程序名,
 Microsoft.Jet.OLEDB.4.0是Office2003中Excel和Access的驱动程序,
 SQLOLEDB是SQL Server的Microsoft OLE DB驱动程序,
 MSDASQL是SQL Server的ODBC OLE DB驱动程序
=====================================================================================================*/

用于Microsoft Windows的版本包括：

• 1987年 Excel 2.0 for Windows
• 1990年 Excel 3.0
• 1992年 Excel 4.0
• 1993年 Excel 5.0
• 1995年 Excel 95 - 亦称 7.0
• 1997年 Excel 97 - 亦称 8.0
• 1999年 Excel 2000 - 亦称 9.0
• 2001年 Excel XP - 亦称 10.0
• 2003年 Excel 2003 - 亦称 11.0
• 2006年 Excel 2007（测试版） - 亦称 12.0

--开启服务器端相关配置(SQL Server 2000默认已开启，SQL Server 2005和SQL Server 2008中未开启)
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

--读取Office2007中Excel和Access中的数据
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=YES;Database=c:/bb.xlsx;','SELECT * FROM [Query$]')
GO
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','C:/aa.accdb';'admin';'',Sample)
GO

--读取Office2003中Excel和Access中的数据
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','EXCEL 8.0;HDR=YES;database=c:/bb.xls',[Query$])
GO
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','EXCEL 8.0;HDR=YES;database=c:/bb.xls','SELECT * FROM [Query$]')
GO
SELECT * FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:/bb.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')...Query$
GO
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','C:/aa.mdb';'admin';'',Sample)
GO
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','c:/aa.mdb';'admin';'','SELECT * FROM Sample')
GO
SELECT * FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:/aa.mdb";User ID=Admin;Password=')...Sample
GO
--读取Excel中Sheet名为Query的A1->B10区间的数据
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=c:/bb.xls;','SELECT * FROM [Query$A1:B10]')
GO

--使用SQL Server 的 Microsoft OLE DB读取本地或远程SQL Sever服务器上数据
SELECT * FROM OPENDATASOURCE('SQLOLEDB','Data Source=(local);User ID=sa;Password=asdfasdf').master.dbo.sysobjects
GO
SELECT * FROM OPENDATASOURCE('SQLOLEDB','Data Source=192.168.108.22/CR2005;User ID=sa;Password=freeborders').master.dbo.sysobjects
GO
--OPENROWSET('SQLOLEDB','服务器地址,端口';'用户名';'密码', 'SQL语句')
SELECT a.*
FROM OPENROWSET('SQLOLEDB','192.168.108.22/CR2005';'sa';'freeborders','SELECT * FROM master.dbo.sysobjects') AS a

--通过ODBC读取本地或远程SQL Sever服务器上数据
--将192.168.0.1端口为2412的SQL SERVER上的table2表和本地服务器上的table1表联接
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
GO
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
GO

--向Office2003中Excel中插入数据
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等
GO
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
GO
--将Office2003中Access中数据插入到SQL Server表中
INSERT INTO [tablename] (fieldname)
SELECT fieldname
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="c:/test.mdb"')...[tablename]