Post reply

insert non duplicates, am i missing something here?

  • August 4, 2009 at 1:26 pm

    #208911

    Ok, i come to think im not an idiot, but maybe i am whats wrong with this query. I just want to insert email addresses from one table into my master table, but i cant have duplicates otherwise my insert statement will fail. here is what i am executing, but SQL Server says duplicate index violation!

    what am i doing wrong? thank you

    insert into emails(email) select t.email from temp1 t

    left outer join emails e on e.email = t.email

    where e.email is null

    Both tables on have one field called email with a unique non clustored index on email

  • August 4, 2009 at 8:53 pm

    #1034596

    The code you presented works just fine with no indexes except on the Email columns. In order for us to figure this out, you'll need to post the table creation scripts along with the indexes. Also, check to see if you have any triggers. Include those if they are present.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 5, 2009 at 12:58 am

    #1034634

    If temp1 contains duplicates, you will get the error. Try using distinct

    insert into emails(email) select distinct t.email from temp1 t

    left outer join emails e on e.email = t.email

    where e.email is null

    Peter

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply