Re: continuous copy/update one table to another

[Szymon Guz <mabewlun@xxxxxxxxx>]

2010/2/28 John R Pierce <pierce@xxxxxxxxxxxx>

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

 

Different doesn't mean that the id should be greater or lower, rather should be different. I'd rather do something like:

insert into table2 select * from table1 as t1 where not exists (select 42 from table2 as t2 where t2.id = t1.id);

of course assuming that the primary key is id;

Another problem is that it wouldn't copy changed records (this should rather be done using some triggers)

Szymon Guz