RE: Better INSERT statement

Depends on what you mean by better.

If you foresee few duplicates, and the file has primary or unique keys defined, 
you might want to use SQL exception handling to just ignore duplicate key 
records.

On the other hand, if you have a lot of duplicates, then what you are doing is 
about as good as you can get.

HTH,

Charles Wilt
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Patrick Conner
> Sent: Thursday, February 24, 2005 3:05 PM
> To: midrange-l@xxxxxxxxxxxx
> 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.
> 
>