FIX: AV on INSERT of UNIONed SELECTs to Table With DEFAULTs (149938)
The information in this article applies to:
- Microsoft SQL Server 6.0
- Microsoft SQL Server 6.5
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.
| Modification Type: | Major | Last Reviewed: | 10/16/2003 |
|---|
| Keywords: | kbBug kbfix kbProgramming kbusage KB149938 |
|---|
|