Re: continuous copy/update one table to another

[John R Pierce <pierce(at)hogranch(dot)com>]

Terry wrote:
> Hello,
>
> I am looking for a way to copy all the data from one table to another
> on a regular basis, every 5 minutes let's say.
>
> INSERT INTO table2 SELECT * FROM table1;
>
> The above will copy all the data as is and insert it into the other
> table.  What happens if I rerun it again?  Will it just append table1
> again into table2?  How can I have it only insert rows that are
> different?  Would that be a program to lookup the most last record in
> table 1 and then a query to only select after that row for the insert
> into table2?
>
>   

both tables should have a serial 'id' (or bigserial if you expect over 2 
billion entries), and use something like ...

     
      insert into table2  select * from table1 as t1 where t1.id > 
(select max(t.id) from table2 as t);


i haven't tested this but I think it should work.  as long as id is 
indexed in both tables