Import data, only want to update new values to a table

[coolkake]

All

I've tried to find an answer in the forums but I need a bit of direction to find my way

I get a .csv file every day with a master list of sites, this list always has new locations. I've created a table that stores this information and it imports just fine, when the table is empty. I want to be able to just add the new sites to this table upon import or via a query if possible.

Hopefully someone understands what I'm look for

Thanks in Advance

Kake

 

[SQLSister]

Sure the best way to do this is import the data into a holding table.

Then run an update statment joining to the holding table to update any existing records

Then run an insert statment using a left join to insert any new records (records in the holding table which are not in the table you are inserting to)

Finally run a delete statement to delete any records which are in the real data, but not the holding table (you may or may not need to do this step depending on the data and wheter you are only getting changes or all the data.)

Here's an example of how I would do it in SQL Server (Your syntax may vary depending on what db you use):

UPDATE table1
SET field1 = h.field1,
field2 = h.field2
FROM table1 JOIN HoldingTable
ON table1.idfield = h.idfield

INSERT INTO table1
SELECT h.field1, h.field2 FROM HoldingTable h LEFT JOIN table1
ON h.idfield = table1.idfield
WHERE table1.idfield IS NULL

DELETE  table1
FROM table1 LEFT JOIN Holdingtable h 
  ON table1.idfield = h.idfield
WHERE h.idfield IS NULL

Questions about posting. See faq183-874

 

[coolkake]

Thanks for the response. Would it be the same in Access 2000? I've never done this in Access, I was hoping for a Wizard

 

[coolkake]

SQLSister

I've tried what your statement and I'm failing on
FROM table1 JOIN HoldingTable

Could you show me this for Access 2000?

Thanks in advance

Kake

 

[SQLSister]

Here's an update I got to work in a test Access database of mine

[UPDATE Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID SET Table2.test1 = [table1].[field1]
WHERE (((Table2.ID) Is Null));

Questions about posting. See faq183-874