2010/2/28 John R Pierce <pie...@hogranch.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 > > 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
