On 12/08/2011 10:32 AM, David Johnston wrote:
> The general structure for the insert would be:
>
> INSERT INTO maintable (cols)
> SELECT cols FROM staging WHERE staging.idcols NOT IN (SELECT
> maintable.idcols FROM maintable);
>
> There may be more efficient ways to write the query but the idea is the
> same.
Yeah... I'd favour an EXISTS test or a join.
INSERT INTO maintable (cols)
SELECT cols FROM staging WHERE NOT EXISTS (SELECT
1 FROM maintable WHERE maintable.idcol = staging.idcol);
... as the NOT IN(...) test can have less than lovely behavior for large
key sets.
--
Craig Ringer
