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
LVL 58
GaryAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Terry WoodsConnect With a Mentor IT GuruCommented:
Building on UnifiedIS's solution, if you have a primary key on more than one column, then you can do it like this:

INSERT INTO Table1(cola, colb)
SELECT cola, colb
FROM table2
WHERE NOT EXISTS (SELECT 1 FROM Table1 WHERE cola = table2.cola and colb = table2.colb)

Open in new window

0
 
UnifiedISConnect With a Mentor Commented:
Something like this should do it:

INSERT INTO Table1(ID)
SELECT ID
FROM table2
WHERE ID NOT IN (SELECT ID FROM Table1)
0
 
snoyes_jwCommented:
Or just use IGNORE.

INSERT IGNORE INTO t1 (id) SELECT id FROM t2;

Open in new window

0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Terry WoodsIT GuruCommented:
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.
0
 
GaryAuthor Commented:
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.
0
 
awking00Commented:
Try running an explain on each solution and look at the output to see which is likely to perform better.
0
 
snoyes_jwCommented:
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.
0
 
GaryAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.