Easy: How to Append/Update into Table?

[eyal8r]

Hey guys-
I'm getting hung up on something here- I have a table which I import all my raw data into. I then need to move that data into other tables (split it up and move some into 1 table, other into another, etc). I have the relationships formed on those new tables...but- I'm getting stuck on teh query to do it.

The raw data is a combination of new records coming in, as well as old records which may or may not be changed from the existing records. I know I need to do an update query- so that it will update the existing records. However, how do I handle the new records? Probably an append query, I'm assuming. So the question is this- do I have to write an if/then statement to do both the update and append queries at the same time? How do I make the query determine if it's an existing record, update it, otherwise if it's a new record, add it? Any examples you can give would be wonderful!
Thank you!

 

[lameid]

So you have data in table X and you want to make table Y have all those records considering that the key may already exist in Y?

Write an update query to Left Join from X to Y. Then update all the fields (especially the key) in Y. By updating the key, it in effect appends the missing records. This works in Jet... I don't think other versions of SQL like it but Jet does.

The other easy way to do it is to append all records (let it fail based on the primary key) and then update all the matching records.

 

[PHV]

Have a look here:
thread701-1053292

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886