Thursday, July 8, 2010

Openrowset Command in SQL

Syntax :

OPENROWSET
( { 'provider_name', { 'datasource';'user_id';'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query'
}
| BULK 'data_file',
{ 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 ]
Import CSV File :

SELECT
'EmployeeInfo.csv' AS FileName
, '.csv' AS FileType
, BulkColumn
FROM OPENROWSET(BULK N'C:\EmployeeInfo.csv', SINGLE_CLOB) AS Document;
Output :
FileName         FileType BulkColumn
---------------- -------- ----------------------------------------------
EmployeeInfo.csv .csv Eugene Malarky,Grove Street Smithtown,New York
Example Of SQL Database :

SELECT JSDTest.*
FROM OPENROWSET('SQLOLEDB','PC\SQLSERVER2008';'sa';'softweb',
'SELECT CategoryName, ProductName
FROM JSDTest.dbo.Product AS Product
INNER JOIN JSDTest.dbo.Category AS Category ON Category.CategoryID = Product.CategoryID
ORDER BY CategoryName, ProductName'
) AS JSDTest
Example of MDB :

SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
AS a
Example of Excel :

SELECT *
INTO db1.dbo.table1
FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\book1.xls',
'SELECT * FROM [sheet1$]')

No comments: