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

coolkake · Dec 8, 2004

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 · Dec 8, 2004

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):

Code:

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 · Dec 13, 2004

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 · Dec 22, 2004

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 · Dec 22, 2004

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

Code:

[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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

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

coolkake

Technical User

SQLSister

Programmer

coolkake

Technical User

coolkake

Technical User

SQLSister

Programmer

Similar threads

Part and Inventory Search

Sponsor