Bypassing DEFAULT Definitions
The bcp utility and the BULK INSERT statement accept the -k switch and the KEEPNULLS clause, respectively, 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 switch 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, you can use the BULK INSERT statement from a query tool, such as SQL 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.