1.文本
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Text;Database=D:\;','SELECT * FROM [aaa.txt]')
2.EXCELL
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=d:\LOG.xls',sheet1$)
3.数据库
1. MSDASQL驱动
SELECT TOP 1 *
FROM OPENROWSET('MSDASQL', 'DRIVER={SQL Server};SERVER=192.168.1.100,1433;UID=myid;PWD=mypassword;', 'select * from AdventureWorks.HumanResources.Employee')
2. SQLNCLI驱动
SELECT TOP 1 *
FROM OPENROWSET('SQLNCLI', 'SERVER=192.168.1.100,1433;UID=myid;PWD=mypassword;', 'select * from AdventureWorks.HumanResources.Employee')
3. SQLOLEDB驱动
SELECT TOP 1 *
FROM OpenRowSet('SQLOLEDB', '192.168.1.100,1433'; 'myid'; 'mypassword', AdventureWorks.HumanResources.Employee)
<二>、使用OPENDATASOURCE
4. MSDASQL驱动
SELECT TOP 1 *
FROM OPENDATASOURCE('MSDASQL', 'DRIVER={SQL Server};SERVER=192.168.1.100,1433;UID=myid;PWD=mypassword;').AdventureWorks.HumanResources.Employee;
5. SQLOLEDB驱动
SELECT TOP 1 *
FROM OPENDATASOURCE('SQLNCLI', 'SERVER=192.168.1.100,1433;UID=myid;PWD=mypassword;').AdventureWorks.HumanResources.Employee;
6. SQLOLEDB驱动
SELECT TOP 1 *
FROM OpenDataSource('SQLOLEDB', 'Data Source=192.168.1.100,1433;User ID=myid;Password=mypassword').AdventureWorks.HumanResources.Employee
二、Access
1. OpenRowSet
SELECT *
FROM OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'c:\student.mdb'; 'Admin'; '', Access表名)
2. OpenDataSource
SELECT *
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source=c:\student.mdb;User ID=Admin;Password=;')...[Grades]
3. ODBC;Driver=SQL Server;
INSERT INTO test(id, tmp)
SELECT id, temptext
FROM [ODBC;Driver=SQL Server;SERVER=192.168.1.100,1433;UID=myid;PWD=mypassword;database=dbname].dbo.tablename;
三、Excel
<一>、使用OpenRowSet(注意当HDR，IMEX设置不同时查询结果的差异)
1. HDR=No; IMEX=0;
SELECT *
FROM OpenRowSet('MICROSOFT.JET.OLEDB.4.0', 'Excel 8.0; HDR=No; IMEX=0; DATABASE=c:\test.xls', WorkSheetName$)
2. HDR=No; IMEX=1;
SELECT *
FROM OpenRowSet('MICROSOFT.JET.OLEDB.4.0', 'Excel 8.0; HDR=No; IMEX=1; DATABASE=c:\test.xls', WorkSheetName$)
3. HDR=Yes; IMEX=0;
SELECT *
FROM OpenRowSet('MICROSOFT.JET.OLEDB.4.0', 'Excel 8.0; HDR=Yes; IMEX=0; DATABASE=c:\test.xls', WorkSheetName$)
4. HDR=Yes; IMEX=1;
SELECT *
FROM OpenRowSet('MICROSOFT.JET.OLEDB.4.0', 'Excel 8.0; HDR=Yes; IMEX=1; DATABASE=c:\test.xls', WorkSheetName$)
<二>、使用OpenDataSource
SELECT *
FROM OpenDataSource('MICROSOFT.JET.OLEDB.4.0', 'Excel 8.0; HDR=No; IMEX=1; DATABASE=c:\test.xls')...[WorkSheetName$]
