T-SQL - OPENROWSET( BULK ...

OPENROWSET
( 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 ]

OPENROWSET supports bulk operations too through a built-in BULK provider
that enables data from a file to be read and returned as a rowset.

BULK
Uses the BULK rowset provider for OPENROWSET to read data from a file.
In SQL Server, OPENROWSET can read from a data file directly which it
lets you use OPENROWSET with a simple SELECT statement.

With bulk options and arguments you can control the start position and
end of reading data, how to deal with errors, and how data is interpreted.

'data_file'
Full path of the data file whose data is to be copied into the target table.

FORMATFILE = 'format_file_path'
Full path of a format file, for more info look at the following URL:
http://msdn.microsoft.com/en-us/library/ms178129.aspx

SINGLE_BLOB
Returns the contents of data_file as a single-row, single-column rowset
of type varbinary(max).

Note: It's recommended only use this option to import XML dat because
only SINGLE_BLOB supports all Windows encoding conversions.

SINGLE_CLOB
It returns the contents as a single-row, single-column rowset of type
varchar(max) using the collation of the current database when you read
an ASCII data file.

SINGLE_NCLOB
It returns the contents as a single-row, single-column rowset of type
nvarchar(max) using the collation of the current database when you read
an UNICODE data file.

Some notes about using OPENROWSET with the BULK Option
OPENROWSET(BULK...) function:

* A FROM clause that is used with SELECT can call OPENROWSET(BULK...)
instead of a table name, with full SELECT functionality.
OPENROWSET with the BULK option requires an alias in the FROM clause.
Column aliases can be specified. If a column alias list is not specified,
the format file must have column names. Specifying column aliases overrides
the column names in the format file, such as:
FROM OPENROWSET(BULK...) AS table_alias
FROM OPENROWSET(BULK...) AS table_alias(column_alias,...n)
* A SELECT...FROM OPENROWSET(BULK...) statement queries the data in a file
directly, without importing the data into a table.
* Using OPENROWSET(BULK...) as a source table in an INSERT or MERGE statement
bulk imports data from a data file into a SQL Server table.
* When the OPENROWSET BULK option is used with an INSERT statement, the BULK
clause supports table hints.

To bulk import data, call OPENROWSET(BULK ..) from a SELECT ... FROM clause within
an INSERT statement. The basic syntax for bulk importing data is:

INSERT ... SELECT * FROM OPENROWSET(BULK...)

Simple examples:

USE test
INSERT INTO myTable (dataFileClmn)
SELECT * FROM OPENROWSET(BULK 'C:\DataFile.txt', SINGLE_NCLOB) as A

USE test
INSERT INTO myXmlFilesTable (xmlFileClmn)
SELECT * FROM OPENROWSET(BULK 'C:\TestXml.xml', SINGLE_BLOB) AS A

T-SQL - OPENROWSET( BULK ...