Home Forums SQL Server 2005 T-SQL (SS2K5) Logic question regarding inserting of rows from 1 table to another. RE: Logic question regarding inserting of rows from 1 table to another.

  • November 4, 2013 at 7:30 am

    #1663814

    roy.tollison (11/1/2013)

    OK after going over this with the users i have been told that duplicate f1 (indexed unique) is possible. how would i go about changing the insert command to avoid getting duplicate error. I need it to not error out but to add all the rows that are not duplicates.

    so t1.f1 = 1 thru 10000 but in t2.f1 they have added values 9000 thru 12000

    i would need the insert command to add all of the rows 1 thru 8999

    i have figured out the 'where t1.f1 not in (select t2.f1 from t2)' gets me around the duplicate error without bombing out. But how do i accomplish the same results if the unique index is multiple columns like f1 and f4 = index1

    i need to avoid adding rows based upon index1 and not f1+f4.

    thanks again.

    Not sure what "based upon index1 and not f1+f4" means, but for index in multiple columns like f1 and f4 you do it much as for the single column case, except that you have to use not exists instead of not in, something like

    where not exists (select 1 from t1 inner join t2 on t1.f1=t2.f1 and t2.f4 = t1.f4)

    Tom