SQL Help Needed 1

[itsjohnnyboy]

Hi -

Need to write a pl/sql block that can quickly insert 10,000 rows in a column. Right now, the column is blank.

(example: table.column
row 1 in column is 1
row 2 in column is 2
row 3 in column is 3
... and so forth..

how would a PL/SQL block be used with a counter to do this?

 

[lewisp]

Look at the FORALL loop command.

 

[sem]

You may fill your table with ROWNUMs from some quite large query. Personally I use

insert into myTable select rownum from dba_objects where rownum<=10000

Regards, Dima

http://seminihin.narod.ru

 

[lewisp]

Dimas excellent solution works fine in all cases. Here is a PL/SQL snippet using FORALL so you can compare for your requirements:

DECLARE
  TYPE l_num_tab IS TABLE OF NUMBER
  INDEX BY BINARY_INTEGER;
--
  l_tab l_num_tab;
BEGIN
  FOR i IN 1..10000
  LOOP
    l_tab(i) := i;
  END LOOP;
--
  FORALL i IN 1..10000
    INSERT INTO table (column)
    VALUES (l_tab(i));
END;

 

[itsjohnnyboy]

lewisp gets a star! Thanks so much, your example gets me exactly what I needed...I started to create a copy of the table, created a sequence on it and then did an insert/select with a trigger on it, but it was taking too long.

Thanks again kind sir!

 

[sem]

So where is it? I mean a star

Regards, Dima

http://seminihin.narod.ru

 

[itsjohnnyboy]

sorry, just sent it!