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


The information in this article applies to:

  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)
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:

Using BCP/BULK INSERT with -k/KEEPNULLSUsing 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.

REFERENCES

SQL Server Books Online; topic: "Modifying Data Through a View"; topic: "bcp Utility"; topic: "BULK INSERT (T-SQL)"
Modification Type:MajorLast Reviewed:10/17/2003
Keywords:kbBug kbpending KB249887
©2003 Microsoft Corporation. All rights reserved.