| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: insert a sequence into a table
On Tue, 29 Jun 1999 12:22:11 +1200, "CL" <charlie_at_xenacom.co.nz> wrote:
>hi there
>I have a table as
>CREATE TABLE tname(sn number(3));
>and then I want to insert value from 1 to 500.
>I created a sequence sq and I can do this:
>INSERT INTO tname VALUES(sq.nextval);
>the problem is how can I use WHERE clause
>to control the number of rows to be inserted?
>Regards
>Charles Luo
>
You can use PL/SQL to do that.
SQL>
1 begin
2 for i in 1 .. 500 loop
3 insert into tname( sn ) values ( sq.nextval );
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select count(*) from tname;
COUNT(*)
500
Or you don't even need a sequence ...
SQL>
1 begin
2 for i in 1 .. 500 loop
3 insert into tname( sn ) values ( i );
4 end loop;
5* end;
6 /
PL/SQL procedure successfully completed.
SQL> select count(*) from tname;
COUNT(*)
500
or you can use a large table( one with at least 500 rows ), say all_objects, and the rownum to do the insert.
SQL>
1 insert into tname( sn )
2 select rownum
3 from all_objects
4* where rownum <= 500;
500 rows created.
SQL> select count(*) from tname;
COUNT(*)
500
hope this helps.
chris.
--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.
 |
 |