Re: continuous copy/update one table to another

[David W Noon <dwnoon(at)ntlworld(dot)com>]

On Sun, 28 Feb 2010 15:56:41 -0600, Terry wrote about [GENERAL]
continuous copy/update one table to another:

>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?

You will get key duplication errors.

>How can I have it only insert rows that are different?

Use an EXISTS predicate:

INSERT INTO table2 SELECT * FROM table1 AS t1
  WHERE NOT EXISTS (SELECT * FROM table2 WHERE table2.key = t1.key);

You will need to compare all fields in the key if it is a multi-column
key.
-- 
Regards,

Dave  [RLU #314465]
=======================================================================
david(dot)w(dot)noon(at)ntlworld(dot)com (David W Noon)
=======================================================================