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
