Ignoring DEFAULT Definitions

The bcp utility accepts the -k parameter, and the BULK INSERT statement accepts the KEEPNULLS clause, which can be used to specify that empty columns should retain a null value during the bulk copy operation, rather than have any default values for the columns inserted.

Note If default values are not inserted, the column must be defined to allow null values.

By default, when data is copied into a table using the bcp utility or BULK INSERT statement, any defaults defined for the columns in the table are observed. For example, if there is a null field in a data file, the default value for the column is loaded instead.

For example, the data file Publishers.txt has two rows:

0111,New Moon Books,Boston,MA,

0222,Binnet & Hardley,Washington,DC,USA

Commas separate the fields; a newline character separates the rows. There is no country for the first row. If the country column of the publishers table had a default of “USA”, the rows bulk loaded into the table by bcp or the BULK INSERT statement when the -k parameter or KEEPNULLS clause is not specified are:

0111   New Moon Books                  Boston                MA      USA

0222   Binnet & Hardley                Washington            DC      USA

Alternatively, to bulk copy data from the Publishers.txt data file into the publishers table in the pubs database and insert the value null into the country column, rather than the default value of “USA”, execute from the command prompt:

bcp pubs..publishers in publishers.txt -c -t, -Sservername -Usa -Ppassword -k

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

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

    DATAFILETYPE = 'char',

    FIELDTERMINATOR = ',',
    KEEPNULLS

)

Note Although DEFAULT definitions on the table are not checked for the bulk copy operation if -k or KEEPNULLS is specified, DEFAULT definitions are expected for other concurrent INSERT statements.

See Also