Microsoft KB Archive/171886: Difference between revisions
(importing KB archive) |
m (Text replacement - """ to """) |
||
Line 78: | Line 78: | ||
<ul> | <ul> | ||
<li><p>INSERT with | <li><p>INSERT with "DISTINCT" and "NOT EXISTS" clauses, as in the following example:<br /> | ||
</p> | </p> | ||
<pre class="codesample"> INSERT INTO t1 (c) | <pre class="codesample"> INSERT INTO t1 (c) | ||
Line 86: | Line 86: | ||
</pre> | </pre> | ||
<p>-or-</p></li> | <p>-or-</p></li> | ||
<li><p>INSERT without using a | <li><p>INSERT without using a "NOT EXISTS" clause, as in the following example:<br /> | ||
</p> | </p> | ||
<pre class="codesample"> INSERT INTO t1 (c) | <pre class="codesample"> INSERT INTO t1 (c) |
Latest revision as of 11:05, 21 July 2020
Article ID: 171886
Article Last Modified on 10/3/2003
APPLIES TO
- Microsoft SQL Server 6.5 Standard Edition
This article was previously published under Q171886
BUG #: 17094
SYMPTOMS
An attempt to insert duplicates into a table with the IGNORE_DUP_KEY option set ON may cause an INSERT INTO command to be aborted. The following scripts demonstrate this problem:
SET NOCOUNT ON DROP TABLE t1 GO DROP TABLE t2 GO CREATE TABLE t1 (c INT) CREATE UNIQUE INDEX ind ON t1(c) WITH IGNORE_DUP_KEY SELECT c = 1 INTO t2 UNION ALL SELECT 1 INSERT INTO t1(c) SELECT c FROM t2 WHERE NOT EXISTS (SELECT * FROM t1 WHERE t1.c = t2.c)
WORKAROUND
To work around this problem, do either of the following:
INSERT with "DISTINCT" and "NOT EXISTS" clauses, as in the following example:
INSERT INTO t1 (c) SELECT DISTINCT c FROM t2 WHERE NOT EXISTS (SELECT * FROM t1 WHERE t1.c = t2.c)
-or-
INSERT without using a "NOT EXISTS" clause, as in the following example:
INSERT INTO t1 (c) SELECT c FROM t2
STATUS
Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.
Additional query words: tsql transql transact-sql
Keywords: kbbug kbusage KB171886