This topic has not yet been rated - Rate this topic

Bulk Importing Large-Object Data

A data file can be bulk-imported as large-object data. The image, text and ntext data types were deprecated with Microsoft SQL Server 2005. The replacement data types are varbinary(max), varchar(max) and nvarchar(max) respectively.

Importing Large Objects by using the OPENROWSET Bulk Rowset Provider

To import large-object data, the OPENROWSET BULK clause supports three options that allow you to import the contents of a data file as a single-row, single-column rowset. You can specify one of the large-object options instead of using a format file.

The large-object options are:

SINGLE_BLOB: Reads the contents of data_file as a single-row, returns the contents as a single-column rowset of type varbinary(max).
SINGLE_CLOB: Reads the contents of the specified data file as characters, returns the contents as a single-row, single-column rowset of type varchar(max), using the collation of the current database; such as a text or Microsoft Word document.
SINGLE_NCLOB: Reads the contents of the specified data file as Unicode, returns the contents as a single-row, single-column rowset of type nvarchar(max), using the collation of the current database.

Reference

BACKUP (Transact-SQL)

OPENROWSET (Transact-SQL)

bcp Utility

BULK INSERT (Transact-SQL)

Concepts

Keeping Nulls or Using Default Values During Bulk Import

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD