FIX: Incorrect Results with Self Insert and Subquery (248441)

SYMPTOMS

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.

-and-
No index exists on the table.

-or-

The TOP and ORDER BY keywords are used in the subquery.

-and-
An index exists on the table.

CAUSE

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.

WORKAROUND

To work around this problem, do not attempt to insert rows based on previous rows inserted in the same query.

STATUS

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.