Thread: advice

Hello,
I have 2 tables with identical schemas.
What I have to do is insert in the 1st table all the rows from the
2nd table that don't exist in the 1st.

I'm planning to something like
INSERT INTO first_table
  SELECT * FROM second_table s WHERE
     NOT EXISTS (SELECT 1 FROM first_table WHERE id = s.id)

but I'm not sure this is the best way to do it...

Would anyone care to suggest a better query to do this ?

Thank you.

Andrei,

> I'm planning to something like
> INSERT INTO first_table
>   SELECT * FROM second_table s WHERE
>      NOT EXISTS (SELECT 1 FROM first_table WHERE id = s.id)

This is the way to do it, athough I'd fully qualify both "id" fields in the
subquery and use an alias to avoid confusion, e.g.
NOT EXISTS (SELECT 1 FROM first_table f2 WHERE f2.id = s.id)

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco

Josh Berkus <josh@agliodbs.com> writes:
>> I'm planning to something like
>> INSERT INTO first_table
>> SELECT * FROM second_table s WHERE
>> NOT EXISTS (SELECT 1 FROM first_table WHERE id = s.id)

> This is the way to do it, athough I'd fully qualify both "id" fields in the
> subquery and use an alias to avoid confusion, e.g.
> NOT EXISTS (SELECT 1 FROM first_table f2 WHERE f2.id = s.id)

Since the tables have identical column sets, you could also think about

INSERT INTO first_table
(SELECT * FROM second_table EXCEPT SELECT * FROM first_table)

which might or might not run considerably faster.  It'd be worth trying
I think.

            regards, tom lane

andrei.ivanov@ines.ro says...

> I'm planning to something like
> INSERT INTO first_table
>   SELECT * FROM second_table s WHERE
>      NOT EXISTS (SELECT 1 FROM first_table WHERE id = s.id)


What about

INSERT INTO first_table T1
(SELECT * FROM second_table T2 WHERE myValue
NOT IN (SELECT myValue FROM T1 WHERE T1.id = T2.id))


Something like that?


Paul...


--

plinehan__AT__yahoo__DOT__com

C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro

Please do not top-post.