OraFAQ Forum: SQL & PL/SQL » insert multiple rows

Home » SQL & PL/SQL » SQL & PL/SQL » insert multiple rows

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

insert multiple rows [message #9633]

Tue, 25 November 2003 07:59

Steve
Messages: 190
Registered: September 1999

Senior Member

I want to insert multiple rows into a given table and use only one statement. How do I do this? I tried:

insert into user_roles
(user_id, role_id)
values (3, 1)
values (3, 2);
But this created a syntax error.

Thanks.

Report message to a moderator

Re: insert multiple rows [message #9634 is a reply to message #9633]

Tue, 25 November 2003 09:29

sverch
Messages: 582
Registered: December 2000

Senior Member

insert into user_roles
(user_id, role_id)
values (3, 1);

insert into user_roles
(user_id, role_id)
values (3, 2);
etc

Report message to a moderator

Re: insert multiple rows [message #9636 is a reply to message #9633]

Tue, 25 November 2003 10:32

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

If you needed to insert roles 1 and 2 for user 3, you could:

insert into user_roles (user_id, role_id)
  select 3, rownum
    from all_objects
   where rownum <= 2;

Report message to a moderator

Re: insert multiple rows [message #9664 is a reply to message #9633]

Thu, 27 November 2003 17:03

William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK

Senior Member

In 9i:

<pre style="color: navy;">SQL> INSERT ALL
2 INTO user_roles VALUES (1,2)
3 INTO user_roles VALUES (3,1)
4 INTO user_roles VALUES (3,2)
5* SELECT dummy FROM dual

3 rows created.

SQL></pre>

Report message to a moderator

Previous Topic:	To convert second into hours,minutes,seconds
Next Topic:	Strange ERROR With UNION

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 07:46:03 CDT 2024