Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/171886

From BetaArchive Wiki
Knowledge Base


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