Home » SQL & PL/SQL » SQL & PL/SQL » inserting multiple rows in single query..
inserting multiple rows in single query.. [message #224105] Tue, 13 March 2007 02:00 Go to next message
v.ram81
Messages: 50
Registered: April 2006
Location: pune
Member

hi, friends
i have a table like num_tab (number(4));
only one column as number datatype .
i want to insert vlues from 1 to 9999 in that table.
how would i insert values in single query ?
this is an interview question.
please help.
thanx in advance..
tc.

regards

ram.
Re: inserting multiple rows in single query.. [message #224108 is a reply to message #224105] Tue, 13 March 2007 02:17 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
insert into <table> select rownum from dba_objects where rownum<=9999

Assuming that you have over 10000 objects in your db, this will probably do it.
Re: inserting multiple rows in single query.. [message #224147 is a reply to message #224108] Tue, 13 March 2007 04:37 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
link

MHE
Re: inserting multiple rows in single query.. [message #224379 is a reply to message #224105] Wed, 14 March 2007 02:21 Go to previous messageGo to next message
harshalonline22
Messages: 74
Registered: March 2007
Location: Pune
Member
mila kya answer?
kis interview me pucha tuze??
Re: inserting multiple rows in single query.. [message #224381 is a reply to message #224379] Wed, 14 March 2007 02:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Please keep this in English.
Re: inserting multiple rows in single query.. [message #224685 is a reply to message #224379] Thu, 15 March 2007 02:36 Go to previous messageGo to next message
harshalonline22
Messages: 74
Registered: March 2007
Location: Pune
Member
Re: inserting multiple rows in single query.. [message #224849 is a reply to message #224105] Thu, 15 March 2007 13:24 Go to previous messageGo to next message
nattoo
Messages: 3
Registered: March 2007
Junior Member
create a sequence with
sql> create sequence s1
start with 1
maxvalue 9999;

sql> insert into temp_table select s1.next_val from dual where rownum>=1 and rownum<=9999;
Re: inserting multiple rows in single query.. [message #224858 is a reply to message #224849] Thu, 15 March 2007 14:50 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
!?!?!
Why would you give an answer that you obviously have not even tried yourself. This is loaded with errors. First off, there is no such thing as next_val. It's nextval.
Next, how many rows did you think your insert command would produce by selecting from DUAL?
Re: inserting multiple rows in single query.. [message #225666 is a reply to message #224105] Tue, 20 March 2007 15:42 Go to previous message
jjr1
Messages: 1
Registered: March 2007
Junior Member
insert into <table>
select level from dual
connect by level<=9999

[Updated on: Tue, 20 March 2007 15:45]

Report message to a moderator

Previous Topic: convert YYYY-MM-DDTHH:MM:SS to YYYY-MM-DD HH:MM:SS
Next Topic: update the table issue
Goto Forum:
  


Current Time: Wed Dec 07 07:02:04 CST 2016

Total time taken to generate the page: 0.13500 seconds