Re: Make MS Access "UPDATE" PostGre SQL Table

Barry,

Per the COPY documentation:

*COPY moves data between PostgreSQL tables and standard file-system files. COPY
TO copies the contents of a table to a file, while COPY FROM copies data
from a file to a table (appending the data to whatever is in the table
already). *

...So it's not destructive. If you simply need to append the data in the CSV
to a table, then COPY might be all you need:

http://www.postgresql.org/docs/8.3/static/sql-copy.html

For updates, one strategy might be to use a "staging" table which you
truncate, COPY FROM the file to the staging table, and then use a stored
procedure to update your main table from there. I haven't benchmarked this,
but I would be shocked if this didn't yield superior performance to a
solution which implements the update logic in the front-end.

Good luck,
Dave

On Mon, Mar 24, 2008 at 9:08 AM, Pettis, Barry <Barry(dot)Pettis(at)atmel(dot)com>
wrote:

> Hi,
>
> I'm fairly new to DBMS's and PostGre SQL in particular. Most of
> my experience is with MS Access. I've created MS Access to retrieve data
> from PostGre by linking tables and have done so without error. However, I
> am now in need of being able to update a PostGre table. The source will be
> a .csv file.
>
>
>
> I've seen a "COPY" command in PostGre that will allow you to
> specify a .csv file as the source, but in looking at it it appears that it
> deletes and then creates the table with the stored data. I need to be able
> to Update or Append data from a file. Maybe going the route of a temp file
> then update from that.
>
>
>
> I'm using Access as that is a point of comfort for me. I can
> write a .net procedure though I'm not exactly firm on the procedure.
>
>
>
> Thoughts on how to do the entire front end in Access.
>
>
>
> Or
>
>
>
> Thoughts on whether to do it from a .net perspective.
>
>
>
>
>
> MSAccess ( 2003 )
>
> PostGreSQL ( 8.1.8 )
>
>
>
>
>
>
>
> Regards,
>
> Barry Pettis
>
> CSO Atmel Corp
>