Article ID: 149938
Article Last Modified on 10/16/2003
APPLIES TO
- Microsoft SQL Server 6.0 Standard Edition
- Microsoft SQL Server 6.5 Standard Edition
This article was previously published under Q149938
BUG #: 14833 (6.00 and 6.50)
SYMPTOMS
INSERTing to a table from a UNION of two or more SELECT statements can result in an Access Violation.
CAUSE
At a certain threshold of data, a work table needs to be created to resolve the UNION correctly. If the target table has DEFAULTs, these are not applied to intermediate work tables but other attributes of the target table, such as NOT NULL, are applied, and this would cause the query to fail with a different error if there was not the AV.
WORKAROUND
Implement the INSERT as distinct INSERT SELECTs for each table in the UNION. If there is a UNIQUE INDEX on the target table, use the IGNORE_DUP_KEY option on that INDEX to filter out duplicate rows that would previously have been filtered out by the UNION. In many cases, this method can be much faster anyway because it can eliminate one or more intermedate work tables.
STATUS
Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 1 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider.
Additional query words: sql60 sql65 union insert select av
Keywords: kbbug kbfix kbprogramming kbusage KB149938
