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)
