TSQL OPENROWSET in SQL Server

By Admin at 28 Sep 2010, 16:10 PM
  • 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.

    1. EXEC sp_configure 'show advanced options', 1  
    2. reconfigure  
    3.   
    4. EXEC sp_configure 'Ad Hoc Distributed Queries', 1  
    5. 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

    1. OPENROWSET  
    2. ( { 'provider_name', { 'datasource';'user_id';'password'  
    3.    | 'provider_string' }  
    4.    , {   [ catalog. ] [ schema. ] object  
    5.        | 'query'  
    6.      }  
    7.    | BULK 'data_file',  
    8.        { FORMATFILE ='format_file_path' [ <bulk_options> ]        | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB } } )<bulk_options> ::=    [ , 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 ]  </bulk_options></bulk_options>  

    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

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

    Excel Example

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

    undefinedundefined

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

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

    undefined

    Text File Example

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

    undefined

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

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

    One more customer file with tab delimited data,

    undefined

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

    1. INSERT INTO #tempCust  
    2. SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Text;Database=C:\DS\',  
    3. '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,

    undefined

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

    1. 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,

    1. SELECT * FROM tempCust  

    undefined

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

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

    Using OPENROWSET with ORDER BY Clause

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

    Result

    undefined

    Using OPENROWSET with WHERE Clause

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

    Result

    undefined

    Using OPENROWSET in UPDATE Statement

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

    Result

    undefined

    Using OPENROWSET in JOIN Statement

    1. CREATE TABLE tmpTbl(CID INT,CName VARCHAR(100))  
    2. INSERT INTO tmpTbl VALUES (1,'Customer123')  
    3. SELECT * FROM tmpTbl  
    4.   
    5. SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','C:\DS\CustomerDB.mdb';'admin';'',tblCustomer)  
    6.   
    7. SELECT * FROM tmpTbl INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0','C:\DS\CustomerDB.mdb';'admin';'',tblCustomer) AS A  
    8. ON A.ID= tmpTbl.CID  

    Result

    undefined


    Comments

     

    Post a comment

    Please correct the following: