Re: ROWNUM question

Others have answered why this occurs. So I thought I would show a way to do what you want. Try:

INSERT INTO TAB1 (SELECT * FROM tab2 WHERE rownum <=250000

                  MINUS
                  SELECT * FROM tab2 WHERE rownum <=100000);

I've never tested it myself, but it's worth a try.

HTH,
Brian

rspeaker_at_my-deja.com wrote:
>
> I have a table with just under 1.5 MM records in it, and wanted to
> insert a subset of those columns and data to another table. I realize
> PL/SQL would be the most efficient way to do this, but at this point, is
> not my forte'. I chose to do it in chunks using SQL*Plus.
>
> I first issued insert into tab1 (select * from tab2 where rownum <
> 100000); to get the first 100,000 records. No problem. I then issued
> insert into tab1 (select * from tab2 where rownum between 100001 and
> 250000); and got 0 records. Then I tried insert into tab1 (select *
> from tab2 where rownum >100000 and rownum <= 250000); and again got 0
> records. Finally I truncated tab1 and did insert into tab1 (select *
> from tab2 where rownum <= 250000); and got 250,000 records.
>
> Can anybody tell me why? Is there something magical about rownum that
> you cannot use it in compound comparisons?
>
> Thanks.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Oct 08 1999 - 09:30:40 CDT