Insert Join Command

[mhalloran]

Hello, I'm struggling to make an import SQL statement. The tables are the same:

table_1
account_number
field1
field2

table_2
account_number
field1
field2

Table 1 contains the online account information and table 2 contains the new data that I want to import. I only want to import the accounts from table 2 that don't already exist on Table 1. Therefor if table 1 has these accounts:
1
2
3
4
and table 2 has these accounts:
2
3
5
9

I want to INSERT accounts 5 and 9 into table 1.

How do I do this?

Here's what I'm doing:
INSERT INTO table_1 (t1.account_number, t1.field1, t1.field2)
SELECT
t2.account_number,t2.field1,t2.field2
FROM table_2 AS t2
LEFT JOIN table_1 AS t1 ON t2.account_number = t1.account_number
WHERE t1.account_number IS NULL;

but it's taking forever. There are over 20K rows and account_number is index on both tables.

Thanks for your help.

[tomhath]

What you have should work. If this is a one time import I wouldn't worry about the time it takes. You might be able to speed it up by inserting into a temporary table first, then selecting from the temporary table and inserting into the permanent one.

[[deleted]]

Thanks for the input. What happened is that I copied a table over for testing and somehow the index wasn't created causing to query to take 30 minutes until I had to kill it.

After adding the index, the query only takes 2 seconds so all is well now. It's a daily update that has to happen during busy hours so I can't have the query hog up the CPU to too long. Thanks.