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?
 
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
All Courses

From novice to tech pro — start learning today.