BUG: BCP Into a Table w/ IDENTITY Column Cannot Be Performed

• Microsoft SQL Server, versions 6.0 and 6.5

SYMPTOMS

A bulk copy into a table with an IDENTITY column cannot be performed when a value for the IDENTITY column has not been specified in the data file.

WORKAROUND

To work around this problem, do one of the following:

• Make sure a value is present for the IDENTITY column in the data file. If the values present in the data file are the actual values to be inserted in the table, use the /E option. If SQL Server-generated values need to be used, run the bulk copy program (BCP) without the /E option.

  -or-

• Perform the following steps:

  1. Create a temporary table without an IDENTITY column.

  2. Bulk copy the data into the table created in step 1.

  3. Use INSERT INTO or SELECT INTO to insert the data into the original

        table from the temporary table.
  

STATUS

Microsoft has confirmed this to be a problem in SQL Server versions 6.0 and 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

BCP IN into a table will work if any value is specified for the IDENTITY column. You can use the /E option when identity values are present within the table to be imported. Without the /E option, the SQL Server will generate identity values for the rows.