UPDATE vs INSERT INTO

[Marty]

Howdy All

I'm writing some VBA code to update a table from a flatfile that was downloaded off the net. Any record in the flatfile may have a primary key that already exists in the table, in which case I think I need to use an SQL "UPDATE" statement to modify the table. Another record, however, may have a primary key value that is entirely new, in which case I think I need to use an SQL "INSERT INTO" statement to update the table. This process of testing to see if a key already exists, then picking between an INSERT INTO versus an UPDATE must be very commonplace. Is there a simplier way to go about the task?

 

[Steve Schapel]

Marty,

I think this will work pretty smoothly if you run your Update Query
first, and the Append Query second. The Update Query will need to
include both the existing table and the inported table, joined on the
the PK field from each, so it will only apply to the imported data where
corresponding records already exist. And the Append Query will just
affect the imported records where a corresponding record does not
already exist and automatically ignore those that do.

 

[Marty Lee]

Hi Steve

Thank you for your reply. At present, I do not have an "imported table". Instead, I was reading the downloaded file, compiling enough data from that file until I had a complete row defined, and then using just that data to update the "existing table". Apparently, the way that I've chosen to think about the update process is out of date. It sounds as if I should read through the download file and simply create a brand new "import table" . Once that is done, I could follow the instructions you gave. Apparently, just one Update Query and just one Append Query should be enough to get the database updated. (As an old fashioned COBOL programmer, I would have expected to code a loop, but I gather this is not the case)

Marty