Constraint Checking

The bcp utility and BULK INSERT statement accept the CHECK_CONSTRAINTS hint and CHECK_CONSTRAINT clause respectively, which allows the user to specify whether constraints are checked during a bulk load.

By default, constraints are ignored during the bulk load. This improves the performance of the bulk load, but allows the possibility of data being inserted into the table that violates existing constraints. CHECK_CONSTRAINTS specifies that constraints are enforced during the bulk load. This reduces the performance of the bulk load, but ensures that all data inserted does not violate any existing constraints. For example, to bulk copy data from the Authors.txt data file to the authors2 table in the pubs database, specifying that any constraints should be enforced, execute from the command prompt:

bcp pubs..authors2 in authors.txt -c -t, -Sservername -Usa -Ppassword -h "CHECK_CONSTRAINTS"

Alternatively, using the BULK INSERT statement from a query tool such as SQL Server Query Analyzer to bulk copy data:

BULK INSERT pubs..authors2 FROM 'c:\authors.txt'
WITH (

    DATAFILETYPE = 'char',

    FIELDTERMINATOR = ',',
    CHECK_CONSTRAINTS

)

When data is copied into a table, any triggers defined for the table are ignored.

To find any rows that violate constraints or triggers, it is necessary to check the copied data manually using queries. Bulk copy data into the table, and run queries or stored procedures that test the constraint or trigger conditions, such as:

UPDATE pubs..authors2 SET au_fname = au_fname

Although this query does not change data to a different value, it causes Microsoft® SQL Server™ to update each value in the au_fname column to itself. This causes any constraints or triggers to be tested.

Note Although, by default, constraints on the table are not checked for the bulk copy operation unless CHECK_CONSTRAINTS is specified, constraints act as expected for other concurrent operations, such as INSERT, UPDATE, or DELETE.

See Also