Microsoft KB Archive/248441

From BetaArchive Wiki
< Microsoft KB Archive
Revision as of 16:14, 18 July 2020 by 3155ffGd (talk | contribs) (importing KB archive)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Knowledge Base

FIX: Incorrect Results with Self Insert and Subquery

Article ID: 248441

Article Last Modified on 3/14/2006


  • Microsoft SQL Server 7.0 Standard Edition

This article was previously published under Q248441

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.

Keywords: kbbug kbfix KB248441