Re: load and unload and update

In article <Cz7AA1.5G8@cix.compulink.co.uk>, shemminga@cix.compulink.co.uk ("Stuart Hemming") writes: |> FWIW, I have a situation where I have to update a customer/address |> database with the output from a (non-informix) accounts package. The way |> I do it is: |> |> create 2 temp tables one with the same schema as the customers table, the |> other with the same schema as the addresses table. |> |> Then, first for customers, then addresses I |> |> remove all records in the live table not in the temp table |> insert all records in the temp table not in the live table |> update all records in the live table with data from the temp table |> |> In this case the data in the temp tables has priority and nothing should |> be in the live database that isn't in the transfer files. |> |> Doing things in this way means that you don't get duplicate record errors |> 'cos you're only inserting stuff that isn't there. |> |> I hope this helps. |> I hope you are doing the processing with transactions. Otherwise, if step 1 suceeds and step 2 fails or somehow the data gets corrupted, you would lose records permanently. Of course, since the data was anyway getting obsolete, probably even if it is deleted, that might be acceptable ? We are curently using that approach, as our data volume is high, and losing a bit of data does not change the overall character of the table. Regards, Ramsub