Microsoft KB Archive/249887
Article ID: 249887
Article Last Modified on 10/17/2003
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft SQL Server 2000 Standard Edition
This article was previously published under Q249887
BUG #: 57191 (SQLBUG_70)
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:
- The base table for the view has a nullable column with defaults.
- There is a view defined for the base table.
- BCP IN is done to the base table through the view.
- 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.
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.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
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:
|Using BCP/BULK INSERT with -k/KEEPNULLS||Using BCP/BULK INSERT without -k/KEEPNULLS|
|The nullable column with default is populated with NULL.||The nullable column with default is populated with the default value.|
SQL Server Books Online; topic: "Modifying Data Through a View"; topic: "bcp Utility"; topic: "BULK INSERT (T-SQL)"
Keywords: kbbug kbpending KB249887