Home Forums SQL Server 2005 T-SQL (SS2K5) INSERT avoiding duplicate error on multi-column unique index RE: INSERT avoiding duplicate error on multi-column unique index

  • roy.tollison (11/1/2013)


    Got the insert part going just trying to figure out how avoid duplicate error.

    i have figured out how to avoid for a single column unique index but how do i accomplish the same thing with a multi-column unique index.

    insert into Table1 (f1, f2, f3, f4, f5, f6)

    select f1, f2, f3, f4, f5, f6 from Table2 where f1 not in (select f1 from Table1)

    seems to work at avoiding duplicate errors but how do i handle it if Table1 has a unique index of f1(int), f3(char(20)), f4(decimal(12,5)) or whatever. Would I get better performance by setting the IGNORE_DUP_KEY = OFF and just do the insert without the where clause or should i figure out how to use the where clause with a multi-column index?

    This would be a lot easier if you would stick to a single thread instead of starting news ones for each step.

    What exactly do you mean here by avoiding duplicates? Do you mean that you can have duplicates in Table2 but you want only the distinct values in Table1?

    Again, ddl and some sample data would go a LONG way to making this easier.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/