Microsoft KB Archive/249887

= BUG: BCP/BULK INSERT Into a View May Cause Defaults Defined in Base-table to be Ignored =

Article ID: 249887

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q249887



BUG #: 57191 (SQLBUG_70)



SYMPTOMS
When using BCP or BULK INSERT to insert data into SQL Server through a view, defaults defined in the base table of the view may be ignored if the following conditions are met:
 * 1) The base table for the view has a nullable column with defaults.
 * 2) There is a view defined for the base table.
 * 3) BCP IN is done to the base table through the view.
 * 4) The rows that are being inserted through BCP have no entries for the column with defaults.

The preceding conditions result in the nullable column, with the default, becoming populated with NULLs instead of with the default value.



WORKAROUND
BCP in through a view is allowed only if one base table is affected. Hence, instead of BCP -ing in through a view, BCP directly into the base table.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



MORE INFORMATION
When using BCP/BULK INSERT to insert data into a table, if a row does not have a value for a nullable column with a default, the column can:
 * Receive a NULL value.
 * Fire the default to populate it with the default value.

BCP/BULK INSERT allows the user to choose between the preceding two behaviors with the help of the -k/KEEPNULLS options respectively. The following table summarizes the behavior when BCP/BULK INSERT is used with and without the -k/KEEPNULLS options to insert a row into a table that does not have a value for the nullable column with a default: