Re: TWO SAME TABLES, ONE UPDATED. HOW TO SYNC THE OTHER?

This query will run quite slow if tables are large, so - you may in addition
create a trigger-updated TIMESTAMP columns and search for changed data
through the recent created/updated elements only.

On 13 May 2007 02:21:30 -0700, rdeleonp(at)gmail(dot)com <rdeleonp(at)gmail(dot)com>
wrote:
>
> On May 11, 11:06 pm, "L. Berger" <straightfwd(dot)(dot)(dot)(at)gmail(dot)com> wrote:
> > Hello
> >
> > I havetwotables-- A and B. The structure of both is thesame. Only,
> > B has many indexes and is used for heavy duty SELECTs. On theother
> > hand, A only accepts heavy duty INSERTs, so has onlyoneprimary key
> > index.
> >
> > So my DB design is such that A is only an INSERT table. Periodically,
> > say every 20 minutes or so, I would like to take all the new INSERTs
> > from table A and put them into B.
> >
> > Is there any clever command to accomplish this? I'd rather not write a
> > PHP script with SQL to take every single new record, and update every
> > column of a new row in table B. For instance, can I do a replication
> > of onlytables, not databases?
> >
> > Thanks for any pointers!!
> >
> > LB
>
> Assuming ID is PK:
>
> INSERT INTO b
> SELECT *
> FROM a
> WHERE NOT EXISTS (
> SELECT 1
> FROM b
> WHERE b.ID = a.ID
> )
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>