On Tue, 2001-11-06 at 11:15, KUCHARSKI, DAVID R. wrote:
> I'm very new to all of this so I'm looking for answers in the SIMPLEST
> of terms. I have a DB that is a multimillion item catalog. Every month
> I get an update to the catalog. The original catalog is maintained by
> someone else running an Oracle system on Windows NT or 2000 servers.
> they output a text file and burn it to cd and mail me the updates as a
> zip file. the tables are converted to .txt windows files as tab
> delimited text. Line one is the header and the rest of the file is the
> contents of the table. Currently I have to go in and strip the headers
> before I can use the data. Then I have to delete the current contents
> of the table and use COPY from to bring in the new information. What
> I'm looking for is a way to get postgreSQL to update the tables from the
> information in the text files and only overwrite what has changed while
> leaving thue unchanged stuff intact. Is my thinking wrong?
> Can it do that?
> if so, HOW? remember I'm very new to this so please be as explicit as
> possible.
One simplification might be to do the COPY into a new (possibly
temporary) table, then you could do a DELETE of rows from your target
which have changed (presumably you can identify which ones somehow -
hopefully there is a date stamp on the incoming records. That done, you
can do a INSERT ... SELECT to get all the changed records from your
temporary-ish table into your target table.
Maybe that sounds complicated, but I think it would be easier to script
than your existing process.
Also, remember that if you're in a GNU textutils environment, tail -n+2
will strip the first line from a file.
Hope this helps,
Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267