Insert where not exists
Not sure of the syntax for this but I want to insert into one Table 1 selected columns from Table 2 where the unique identifier in Table 2 does not exist in Table 1
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The IGNORE option will only work if there is a primary key or unique index on the table being inserted into that will be violated with the combination of columns used. eg if t1 had a primary key on cola, colb and colc (referring to my example above) then more rows may be inserted than intended, but if the primary key was on cola and colb, then it would work.
ASKER
Just before I accept/split points I'd be interested into which of Terry's and Unified's solutions have less overhead. From looking at it they both are minimising ignoring duplicates equally well. Maybe Terry's marginally as it only selecting unique row but probably doesn't matter that much in terms of memory.
As for the IGNORE solution, not efficient as it is trying to insert every row and I have over 5000 rows to go thru.
As for the IGNORE solution, not efficient as it is trying to insert every row and I have over 5000 rows to go thru.
Try running an explain on each solution and look at the output to see which is likely to perform better.
It's true that the IGNORE method requires there to be a primary or unique key to collide with. But don't dismiss it as inefficient until you compare actual run times.
ASKER
Thanks
Open in new window