FIX: Incorrect Results with Self Insert and Subquery

This article was previously published under Q248441
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 54744 (SQLBUG_70)
An INSERT statement in a table from a subquery on the same table gives incorrect results if either of the following sets of conditions is true:
  • The DISTINCT keyword is used in the subquery.

  • No index exists on the table.
  • The TOP and ORDER BY keywords are used in the subquery.

  • An index exists on the table.
This is a Halloween problem when reading data just inserted in the same query. Normally, it is not possible to insert values in a table based on the previous row inserted in the same query. Halloween protection is needed to prevent a situation where the physical location of a row within a table changes due to an UPDATE operation. As a result, the same row may be revisited multiple times within the context of a single logical operation, which should not occur. If the query table and query does not have the previous condition, there is no bug.
To work around this problem, do not attempt to insert rows based on previous rows inserted in the same query.
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
For more information, contact your primary support provider.

Article ID: 248441 - Last Review: 11/02/2013 03:21:00 - Revision: 3.0

Microsoft SQL Server 7.0 Standard Edition

  • kbnosurvey kbarchive kbbug kbfix KB248441