Data Integrity for Defaults, Rules, and Triggers

To ensure integrity, bcp handles data to copy depending upon its element.

Element Type	Description
Defaults and datatypes	When copying data into a table, bcp observes any defaults defined for the columns and datatypes. That is, if there is a null field in the data in a file, bcp loads the default value instead of the null value during the copy. For example, here are two rows in a file to be loaded into `authors`: 409-56-7008,Bennet,David,415 658-9932,622 Pine St.,Berkeley,CA,USA,94705213-46-8915,Green,Marjorie,,309 63rd St. #411,Oakland,CA,USA,94618 Commas separate the fields; a newline terminator separates the rows. There is no phone number for Marjorie Green. Because the `phone` column of the `authors` table has a default of “unknown,” the rows in the loaded table look like this: 409-56-7008 Bennet David 415 658-9932 622 Pine St. Berkeley CA USA 94705 213-46-8915 Green Marjorie unknown 309 63rd St. #411 Oakland CA USA 94618
Rules and triggers	bcp, to enable its maximum speed for loading data, does not fire rules and triggers. To find any rows that violate rules and triggers, copy the data into the table and run queries or stored procedures that test the rule or trigger conditions.

Element Type

Description

Defaults and datatypes

When copying data into a table, bcp observes any defaults defined for the columns and datatypes. That is, if there is a null field in the data in a file, bcp loads the default value instead of the null value during the copy.

For example, here are two rows in a file to be loaded into authors:

409-56-7008,Bennet,David,415 658-9932,622 Pine
St.,Berkeley,CA,USA,94705213-46-8915,Green,Marjorie,,309 63rd St.
#411,Oakland,CA,USA,94618

Commas separate the fields; a newline terminator separates the rows. There is no phone number for Marjorie Green. Because the phone column of the authors table has a default of “unknown,” the rows in the loaded table look like this:

409-56-7008  Bennet  David     415 658-9932  622 Pine St.
    Berkeley  CA  USA  94705
213-46-8915  Green   Marjorie  unknown       309 63rd St. #411
    Oakland  CA  USA  94618

Rules and triggers

bcp, to enable its maximum speed for loading data, does not fire rules and triggers.

To find any rows that violate rules and triggers, copy the data into the table and run queries or stored procedures that test the rule or trigger conditions.