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

Last reviewed: October 24, 1997
Article ID: Q175624
The information in this article applies to:
  • 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.

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 : SSrvGen kbinterop
Version : WINNT:6.0 6.5
Platform : winnt
Issue type : kbprb
Solution Type : kbworkaround


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: October 24, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.