I'm hoping a seasoned SQL DBA here could give me some direction on a SQL
update that I'm trying to write in a stored procedure.

I have two tables, like this:

NEW_EMAIL_ADDRESS
Address Varchar(100)

KNOWN_EMAIL
KE_ID Integer (Primary Key),
Address Varchar(100)

LIST
LIST_ID Integer (Primary Key),
LIST_NAME Varchar(20)

LIST_MEMBERSHIP
LM_ID Integer (Primary Key),
FK_KE_ID Integer (Foreign Key to Known Email),
FK_LIST_ID Integer (Foreign Key to List)


I need to create LIST_MEMBERSHIP records where they don't already exist for
any NEW_EMAIL_ADDRESS. Basically I load in a new list of email addresses,
and parse through them to find out if they are already known. If not, I add
them to the KNOWN_EMAIL table.

Then for each EMAIL_ADDRESS, I need to find the KNOWN_EMAIL address that
matches it, and the LIST that the email is supposed to be a member of
(LIST_ID). I then, if it doesn't already exist, have to add it to the
LIST_MEMBERSHIP table, using its foreign keys for KNOWN_EMAIL and LIST_ID.
If it already does exist, I basically leave it alone.

I have this working by doing it in a PHP application that runs a loop, but
its incredibly slow. Its taking about an hour to parse through 30,000
records, which isn't going to work. It would just make sense to do this in
a stored procedure, but other than basic FOR SELECT and UPDATE/INSERT
procedures which I do routinely, I haven't had to do something as complex as
this before.

What is the best approach with something like this? Could it be done with
one single UPDATE query? Currently I have a stored procedure written that
takes care of moving the records into KNOWN_EMAIL ok, but it's the
subscription to the list part that I'm not sure about.

All pointers, help, advice is greatly appreciated.

Thanks
Myles



===============================
Myles Wakeham
Director of Engineering
Tech Solutions USA, Inc.
Scottsdale, Arizona USA
www.techsolusa.com
Phone +1-480-451-7440