Using bulk insert for 2000 rows of data

Question

Hi,

Would using bulk insert for 2000 rows of data make sense?

It might be 500-2K in reality.

BTW, does bulk inserts ignore constraint or is that a setting?

(using sql server 2008, .net on the server side, data is coming in via a web service (wse or WCF)).

Answer 1

Bulk insert would probably not make sense for 2000 rows. Maybe for 200,000 rows.

Ignoring constraints is default behaviour. (Also described here).

CHECK_CONSTRAINTS

Specifies that all constraints on the target table or view must be checked during the bulk-import operation. Without the CHECK_CONSTRAINTS option, any CHECK and FOREIGN KEY constraints are ignored, and after the operation, the constraint on the table is marked as not-trusted.

Note: UNIQUE, PRIMARY KEY, and NOT NULL constraints are always enforced.

The "KEEPIDENTITY" option of "BULK INSERT":

Specifies that identity value or values in the imported data file are to be used for the identity column. If KEEPIDENTITY is not specified, the identity values for this column are verified but not imported and SQL Server automatically assigns unique values based on the seed and increment values specified during table creation.