Microsoft KB Archive/171886

= BUG: Command Aborted When Inserting into a IGNORE_DUP_KEY Table =

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

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.