RE: Better INSERT statement

This will take for ever if file is big.
How about:
Step 1.
Create a WRKQRY on your LIB2/FILEA with output file say FILDUP : 
1. Select Report Summary Functions for Key fields by COUNT.
2. Define Report Breaks for keyfields 
3. From Outfile FILDUP select only those records with COUNT > 1

-WRKQRY should give following result much faster you can't believe!  
select key1, key2, key3, key4, key5, char(key6),count(*) from LIB2/FILEA
GROUP BY key1, key2, key3, key4, key5, key6
HAVING count(*) > 1......OUTFILE=FILEDUP

so, FILEDUP has all duplicate records you dont want.

Step 2.
Insert into LIB1/FILEA 
        select * from LIB2/FILEA where 
                (key1 || key2 || key3 || key4 || key5 || char(key6))
                     not in (select (key1|| key2 || key3 || key4 || key5 || 
char(key6))
                             from FILDUP)

Rgds,
Sachin

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Dan Kimmel
Sent: Thursday, February 24, 2005 3:56 PM
To: Midrange Systems Technical Discussion
Subject: Re: Better INSERT statement


How about:

insert into lib1.file1
  select * from lib2.file1 a
   where (select "x" from lib1.file1 b where b.key1=a.key1 
                and b.key2=a.key2 and b.key3=a.key3 and
            b.key4=a.key4 and b.key5=a.key5 and b.key6=a.key6) = null


Does that work any better?

Dan





Patrick Conner <PWConner@xxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
02/24/2005 02:05 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
midrange-l@xxxxxxxxxxxx
cc

Subject
Better INSERT statement






Is there a better way to insert records when wanting to add records to a
file from an identical file while avoiding duplicate records?

insert into lib1.file1
  select * from lib2.file1
   where (key1 || key2 || key3 ||
          key4 || key5 || char(key6))
     not in (select
              (key1|| key2 || key3 ||
               key4 || key5 || char(key6))
             from lib1.file1)

-- 
Privileged and Confidential.  This e-mail, and any attachments there to, 
is intended only for use by the addressee(s) named herein and may 
contain legally privileged or confidential information.  If you have 
received this e-mail in error, please notify me immediately by a return 
e-mail and delete this e-mail.  You are hereby notified that any 
dissemination, distribution or copying of this e-mail and/or any 
attachments thereto, is strictly prohibited.


-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.