TSQL OPENROWSET in SQL Server - SQL Programmers

TSQL OPENROWSET

OPENROWSET method is a onetime ad hoc method to connect and access the remote data from an OLEDB data source. Syntax for OPENROWSET contains all information required to access remote data from an OLEDB data source.

OPENROWSET is an alternative to linked servers. By using the OPENROWSET function we can retrieve data from any data sources that support a registered OLEDB provider, such as a remote instance of SQL Server, Microsoft Access, Excel file, Text file, or CSV file.

In SQL server, ad hoc queries must be enabled using sp_configure in order to use the OPENROWSET method.

EXEC sp_configure 'show advanced options', 1
reconfigure

EXEC sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure

Result

When the above script is executed the result will be

• Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
• Configuration option 'Ad Hoc Distributed Queries' changed from 0 to 1. Run the RECONFIGURE statement to install.

If you have the options already enabled then it will show the following message,

• Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
• Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.

Syntax

OPENROWSET
( { 'provider_name', { 'datasource';'user_id';'password'
   | 'provider_string' }
   , {   [ catalog. ] [ schema. ] object
       | 'query'
     }
   | BULK 'data_file',
       { FORMATFILE ='format_file_path' [  ]        | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB } } ) ::=    [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]    [ , ERRORFILE ='file_name' ]    [ , FIRSTROW = first_row ]    [ , LASTROW = last_row ]    [ , MAXERRORS = maximum_errors ]    [ , ROWS_PER_BATCH =rows_per_batch ]  [ , ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) [ UNIQUE ]  

Here,

• 'provider_name' is OLEDB provider name.
• 'datasource' is a constant value that represents the OLEDB data source.
• 'user_id' is a constant value which is passed as a login name for the OLEDB datasource.
• 'password' is a constant value which is passed as a password for the OLEDB datasource.

Example

CREATE TABLE #tempCust(CID INT,CName VARCHAR(100))

Excel Example

Consider the excel file “C:\DS\Cust.xls” with the following data,

Using OPENROWSET method values inserted to the temp table as follows,

INSERT INTO #tempCust
SELECT * FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 4.0;Database=C:\DS\Cust.xls','SELECT * FROM [Sheet1$]')

SELECT * FROM #tempCust

Text File Example

Consider the CSV file “C:\DS\Customer.txt” with the following data,

Using OPENROWSET method, values are inserted from the text file Customer.txt to the temp table as follows:

INSERT INTO #tempCust
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Text;Database=C:\DS\',
'SELECT * FROM Customer.txt')

One more customer file with tab delimited data,

Using OPENROWSET method values inserted from the text file Customer1.txt to temp table as follows,

INSERT INTO #tempCust
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Text;Database=C:\DS\',
'SELECT * FROM Customer1.txt')

We obtain the same result from both cases.

MS Access File Example

Consider the access file “C:\DS\CustomerDB.mdb” with the following data,

Using OPENROWSET method, Records from a table of MS Access database file can be inserted into the SQL Server database table as follows,

INSERT INTO #tempCust SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','C:\DS\CustomerDB.mdb';'admin';'',tblCustomer)  

SQL Example

Now let’s look at the database Test in the server named “Server1”, there should be a table named tempCust in the Text DB,

SELECT * FROM tempCust

Using OPENROWSET method, Data from one SQL Server database can be inserted into another SQL Server database as follows,

SELECT * FROM OPENROWSET('SQLNCLI', 'Server=Server1;Trusted_Connection=yes;', 
     'SELECT * FROM Test.dbo.tempCust ') AS a

Using OPENROWSET with ORDER BY Clause

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Text;Database=C:\DS\',
'SELECT * FROM Customer.txt') ORDER BY NAME DESC

Result

Using OPENROWSET with WHERE Clause

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Text;Database=C:\DS\',
'SELECT * FROM Customer.txt') WHERE NAME ='Customer2'

Result

Using OPENROWSET in UPDATE Statement

UPDATE OPENROWSET('Microsoft.Jet.OLEDB.4.0','C:\DS\CustomerDB.mdb';'admin';'',tblCustomer)  SET Name = Name + '123'

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','C:\DS\CustomerDB.mdb';'admin';'',tblCustomer)  

Result

Using OPENROWSET in JOIN Statement

CREATE TABLE tmpTbl(CID INT,CName VARCHAR(100))
INSERT INTO tmpTbl VALUES (1,'Customer123')
SELECT * FROM tmpTbl

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','C:\DS\CustomerDB.mdb';'admin';'',tblCustomer)

SELECT * FROM tmpTbl INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0','C:\DS\CustomerDB.mdb';'admin';'',tblCustomer) AS A
ON A.ID= tmpTbl.CID

Result