PRB: Error Attempting to Insert Explicit Values with an Append Query

ID: Q175624

The information in this article applies to:

Microsoft SQL Server versions 6.0, 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.

This message appears if both of the following conditions are true:

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.

Note that this problem occurs even if explicit values for the identity column are not specified.

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.
Destination table: TABLE_D with columns colA, colB, colC, colD with colA being an IDENTITY column and the rest allowing null values.

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)

When both col1 and col2 are null for a particular row, the SQL statement that goes into SQL Server would include all the columns in the destination table, thereby trying to insert NULL values into every column, as in the following:


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

This happens even though colA and colD are not specified in the append query.

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.

Additional query words: col

Keywords : kbinterop SSrvGen
Version : WINNT:6.0 6.5
Platform : winnt
Issue type : kbprb