学步园

A. 将 OPENROWSET 用于 SELECT 和 SQL Native Client OLE DB 访问接口

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
     'SELECT GroupName, Name, DepartmentID
      FROM AdventureWorks.HumanResources.Department
      ORDER BY GroupName, Name') AS a;

B. 使用 Microsoft OLE DB Provider for Jet

SELECT CustomerID, CompanyName
   FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
      'C:/Program Files/Microsoft Office/OFFICE11/SAMPLES/Northwind.mdb';
      'admin';'',Customers)
GO

C. 使用 OPENROWSET 和 INNER JOIN 中的另一个表

USE Northwind  
GO
SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c 
   INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'C:/Program Files/Microsoft Office/OFFICE11/SAMPLES/Northwind.mdb';'admin';'', Orders)    
   AS o 
   ON c.CustomerID = o.CustomerID 
GO

D. 使用 OPENROWSET 将文件数据大容量插入 varbinary(max) 列中

USE AdventureWorks
GO
CREATE TABLE myTable(FileName nvarchar(60), 
  FileType nvarchar(60), Document varbinary(max))
GO

INSERT INTO myTable(FileName, FileType, Document) 
   SELECT 'Text1.txt' AS FileName, 
      '.txt' AS FileType, 
      * FROM OPENROWSET(BULK N'C:/Text1.txt', SINGLE_BLOB) AS Document
GO