IRowsetFastLoad Rowsets

SQLOLEDB bulk copy rowsets are write-only, but the rowset exposes interfaces that allow the consumer to determine the structure of a Microsoft® SQL Server™ table. The following interfaces are exposed on a bulk copy-enabled SQLOLEDB rowset:

IAccessor
IColumnsInfo
IColumnsRowset
IConvertType
IRowsetFastLoad
IRowsetInfo
ISupportErrorInfo

The provider-specific properties SSPROP_FASTLOADOPTIONS, SSPROP_FASTLOADKEEPNULLS, and SSPROP_FASTLOADKEEPIDENTITY control behaviors of a SQLOLEDB bulk-copy rowset. The properties are specified in the rgProperties member of an rgPropertySets IOpenRowset parameter member.

Property ID	Description
SSPROP_FASTLOADKEEPIDENTITY	Column: No R/W: Read/write Type: VT_BOOL Default: VARIANT_FALSE Description: Maintains identity values supplied by the consumer. VARIANT_FALSE: Values for an identity column in the SQL Server table are generated by SQL Server. Any value bound for the column is ignored by SQLOLEDB. VARIANT_TRUE: The consumer binds an accessor providing a value for a SQL Server identity column. The identity property is not available on columns accepting NULL, so the consumer provides a unique value on each IRowsetFastLoad::Insert call.
SSPROP_FASTLOADKEEPNULLS	Column: No R/W: Read/write Type: VT_BOOL Default: VARIANT_FALSE Description: Maintains NULL for columns with a DEFAULT constraint. Affects only SQL Server columns that accept NULL and have a DEFAULT constraint applied. VARIANT_FALSE: SQL Server inserts the default value for the column when the SQLOLEDB consumer inserts a row containing NULL for the column. VARIANT_TRUE: SQL Server inserts NULL for the column value when the SQLOLEDB consumer inserts a row containing NULL for the column.
SSPROP_FASTLOADOPTIONS	Column: No R/W: Read/write Type: VT_BSTR Default: none Description: This property is same as the -h “hint[,...n]” option of the bcp utility. The following string(s) can be used as option(s) to be used in the bulk copying of data into a table. ORDER(column[ASC \| DESC][,...n]) Sort order of data in the data file. Bulk copy performance is improved if the data file being loaded is sorted according to the clustered index on the table. ROWS_PER_BATCH = bb Number of rows of data per batch (as bb). The server optimizes the bulk load according to the value bb. By default, ROWS_PER_BATCH is unknown. KILOBYTES_PER_BATCH = cc Number of kilobytes (KB) of data per batch (as cc). By default, KILOBYTES_PER_BATCH is unknown. TABLOCK A table-level lock is acquired for the duration of the bulk copy operation. This option significantly improves performance because holding a lock only for the duration of the bulk copy operation reduces lock contention on the table. A table can be loaded by multiple clients concurrently if the table has no indexes and TABLOCK is specified. By default, the locking behavior is determined by the table option table lock on bulk load. CHECK_CONSTRAINTS Any constraints on table_name are checked during the bulk copy operation. By default, constraints are ignored.

Property ID

Description

SSPROP_FASTLOADKEEPIDENTITY

Column: No
R/W: Read/write
Type: VT_BOOL
Default: VARIANT_FALSE
Description: Maintains identity values supplied by the consumer.

VARIANT_FALSE: Values for an identity column in the SQL Server table are generated by SQL Server. Any value bound for the column is ignored by SQLOLEDB.

VARIANT_TRUE: The consumer binds an accessor providing a value for a SQL Server identity column. The identity property is not available on columns accepting NULL, so the consumer provides a unique value on each IRowsetFastLoad::Insert call.

SSPROP_FASTLOADKEEPNULLS

Column: No
R/W: Read/write
Type: VT_BOOL
Default: VARIANT_FALSE
Description: Maintains NULL for columns with a DEFAULT constraint. Affects only SQL Server columns that accept NULL and have a DEFAULT constraint applied.

VARIANT_FALSE: SQL Server inserts the default value for the column when the SQLOLEDB consumer inserts a row containing NULL for the column.

VARIANT_TRUE: SQL Server inserts NULL for the column value when the SQLOLEDB consumer inserts a row containing NULL for the column.

SSPROP_FASTLOADOPTIONS

Column: No
R/W: Read/write
Type: VT_BSTR
Default: none
Description: This property is same as the -h “hint[,...n]” option of the bcp utility. The following string(s) can be used as option(s) to be used in the bulk copying of data into a table.

ORDER(column[ASC | DESC][,...n])
Sort order of data in the data file. Bulk copy performance is improved if the data file being loaded is sorted according to the clustered index on the table.

ROWS_PER_BATCH = bb
Number of rows of data per batch (as bb). The server optimizes the bulk load according to the value bb. By default, ROWS_PER_BATCH is unknown.

KILOBYTES_PER_BATCH = cc
Number of kilobytes (KB) of data per batch (as cc). By default, KILOBYTES_PER_BATCH is unknown.

TABLOCK
A table-level lock is acquired for the duration of the bulk copy operation. This option significantly improves performance because holding a lock only for the duration of the bulk copy operation reduces lock contention on the table. A table can be loaded by multiple clients concurrently if the table has no indexes and TABLOCK is specified. By default, the locking behavior is determined by the table option table lock on bulk load.

CHECK_CONSTRAINTS
Any constraints on table_name are checked during the bulk copy operation. By default, constraints are ignored.