Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2005
    Posts
    319

    Unanswered: How do you create a constraint to ignore duplicate inserts without throwing an error?

    Title says it all, I know I did this before at my last job, but I can't seem to find the answer online.

    How do you create a constraint to ignore duplicate inserts, without throwing an error?

    Is there any side effects or performance issues I should be worried about?

    I am not worried about inserts falling by the way side, this is a multi-step process where a different thread has updated the value of the index.

  2. #2
    Join Date
    Jun 2005
    Posts
    319
    Answered my own question, posting for anyone interested:

    Creating Unique Indexes

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Thank you for pointing me to this IGNORE_DUP_KEY Option.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    not sure if you care but in my experience, IGNORE_DUP_KEY does NOT work for bulk loads using BCP.

    that is, if you are bulk loading a table from a file and the file has dupes, and the table has a unique key or PK with IGNOR_DUP_KEY on, then BCP will fail.

  5. #5
    Join Date
    Sep 2011
    Posts
    71
    Just handle the error.

    USE Northwind
    GO

    SET NOCOUNT ON
    CREATE TABLE myTable99(Col1 int, Col2 int, Col3 char(1), PRIMARY KEY (Col1, Col2))
    GO

    INSERT INTO myTable99(Col1,Col2,Col3)
    SELECT 1,1,'a' UNION ALL
    SELECT 1,2,'b' UNION ALL
    SELECT 1,3,'c'
    GO

    INSERT INTO myTable99(Col1,Col2,Col3)
    SELECT 1,1,'a'

    IF @@ERROR <> 0 GOTO Error

    PRINT 'No Capture'
    GOTO Leave

    Error:

    PRINT 'Capture'

    Leave:
    GO

    DROP TABLE myTable99
    GO

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •