Re: dynamic insert querry

From: Arul Ramachandran <contactarul_at_gmail.com>
Date: Sun, 20 Mar 2005 18:21:36 -0600
Message-ID: <1c1a629905032016214548b04@mail.gmail.com>

see if this helps ...no pl/sql here ... no counter ...instead sql with analytic function ....

---

create table target as select * from source where 1=2 /
alter table target nologging
/
insert /*+ append */ into target
select batch_id, <all other columns> from   ( select batch_id, <all other columns>,

           row_number() over ( partition by batch_id order by batch_id ) rown     from source )
 where rown < 1001
/

---

-Arul

On Sat, 19 Mar 2005 08:37:24 -0500, Bob Metelsky <bobmetelsky_at_comcast.net> wrote:
> All - I need to build an insert statement for the following condition
>
> I have a column with batch_ids with counts from 1-500k
> I want to build an insert statement that will pull n# of rows
>
> So I want to insert 1000 rows of each batch id (dosnt matter which) I
> just need a sampling of the data
>
> goal
> insert into table
> select * from source where batch_id = 123 and count(batch_id) between
> max(count(batch_id) ) -1000 ;
>
> this is what I have so far, its messy and still needs work
>
> select 'insert into TARGET select * from SOURCE where batch_id =
> '||batch_id|| '
> and count(batch_id) BETWEEN' ,count(batch_id)|| ' AND 100;'
> from SOURCE where rownum <=10 group by batch_id;
>
> There must be a more graceful way to do this <g>
>
> thanks !
> Bob
>
> --
> "Oracle error messages being what they are, do not
> highlight the correct cause of fault, but will identify
> some other error located close to where the real fault lies."
>
> --
> http://www.freelists.org/webpage/oracle-l
>

-- 
Arul
--
http://www.freelists.org/webpage/oracle-l

Received on Sun Mar 20 2005 - 19:25:28 CST