Re: Best way to load differential rows on table with composite key

On Tue, 29 Sep 1998 23:18:40 +1000, Clint Good <clintg@vipnet.com.au> wrote: >Kevin wrote: >> >> Sorry I wasn't very specific, again attribute to Mon morning, I'm trying to >> do this with SQL. >> Thanks again! >> Kevin >> >> Kevin wrote in message <6uo7p1$b46$1@news1-alterdial.uu.net>... >> >Hey! >> >I'm trying to load the rows from table a that are not in table b, into >> table >> >b. >> >The table has a composite key and has a large number of rows. I'm sure this >> >is a no brainer, but seeing as it's Mon morning I think I qualify. >> >Thanks for any help! >> >Kevin > >Hi Kevin > >I'm new to informix, but the following works under most other platforms >I've seen > >insert into b > Select * > From a > Where Not Exist > (Select c.key1, c.key2 > From b c > Where c.key1 = a.key1 and c.key2 = a.key2) This probably works on IDS 7.3 but in earlier versions it wasn't allowed to do a select from the same table you did the insert into. (I don't remember for sure if it is in 7.3 either or that is a feature for some future version.) Also on Informix and I would assume on most other platforms the "not exist" is usually slow. Art's suggestion is the normal and usually fastest way of doing this. Nils Myklebust NM Data AS Norway E-mail: Nils.Myklebust@nmdata.com FAQ at: http://www.iiug.org/techinfo/faq/faq_top.html (Now with ODBC info under "Third party products".)