Re: MINUS works, but is there a faster way?

In article <75sl3g$avi$1_at_winter.news.rcn.net>,   "John Mason Jr" <masonj_at_erols.com> wrote:
> Hi,
> I have two identical tables, same data types and columns. Currently I use
> the following query to select the records that are in the first table but
> not the second.
>
> INSERT INTO schema2.tablename
> SELECT * FROM schema1.tablename
> MINUS
> SELECT * FROM schema2.tablename
>
> There are 10 columns in each table and there is a composite index on 4 of
> the columns. The other six fields contain data that is similar, so I don't
> think adding another index would help.
>
> I tried
>
> select * form schema1.tablename,schema2.tablename
> where
> schema1.tablename.column1<>schema2.tablename.column1
> and
> schema1.tablename.column2<>schema2.tablename.column2
> and
> schema1.tablename.column3<>schema2.tablename.column3
> and
> schema1.tablename.column4<>schema2.tablename.column4;
>
> This didn't work!
>
> Schema1 table is imported form a VAX daily, and only has data for two days,
> schema2 table on NT is for longer term storage of data
>
> What is the proper way to find the all of the records in one table that are
> not in the second?
>
>

You could try something like this:
insert into schema2.tablename
select tablename_pk ,rest of columns from schema1.tablename A

   where not exists (select 'x' from schema2.tablename B

                     where B.tablename_pk = A.tablename_pk);

--
Mladen Gogala

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Dec 24 1998 - 19:20:24 CST