PRB: Error Attempting to Insert Explicit Values w/ Append Query

• Microsoft SQL Server versions 6.0 and 6.5

SYMPTOMS

An append query from a Microsoft Access 97, Access 95, or Access 2.0 database to a table in SQL Server 6.0 or SQL Server 6.5 may generate the following message:

   Attempting to insert explicit value for identity column in <tablename>
   when identity_insert is set to off.

• The append query tries to insert a record from the source table that has NULLs for all the columns, or the selected data from the source table has rows that have NULLs for all the columns.

  -and-

• The destination table in the SQL Server database has a column with the IDENTITY property.

CAUSE

Jet handles the append query request in a different way for rows when all the columns contain NULL values. For an example, see the MORE INFORMATION section of this article.

WORKAROUND

To work around this problem, make sure that the rows from the source table being inserted into the destination table do not have all NULL values. You can do this by adding the criteria "IS NOT NULL" for any particular column in the append query.

MORE INFORMATION

Suppose you have the following two tables:

Source table     : TABLE_S with columns col1, col2, col3 allowing NULLs.

Col1 and col2 from TABLE_S are being selected and appended to TABLE_D with colB and colC as the corresponding columns.

In the normal case, when either col1 or col2 for a particular row has a non- NULL value, the SQL statement that would go into SQL Server would be:

   Insert into <owner>.TABLE_D (colB, colC) values (val1,val2)

   Insert into <owner>.TABLE_D (colA,colB,colC,colD) values
   (NULL,NULL,NULL,NULL)

Because colA in TABLE_D has an IDENTITY property, SQL Server returns the following error message and the append query fails:

   Attempting to insert explicit value for identity column in <tablename>
   when identity_insert is set to off.