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 #183709]

Sat, 22 July 2006 09:00

parmaleuday
Messages: 9
Registered: July 2006
Location: pune

Junior Member

hi everybody

plz tell me how to insert multiple rows into table with a easier way,in the oracle book it is in very difficult way.

Report message to a moderator

Re: how to insert multiple rows into table [message #183712 is a reply to message #183709]

Sat, 22 July 2006 09:56

Frank Naude
Messages: 4581
Registered: April 1998

Senior Member

An insert is as simple as issuing an INSERT INTO statement.

INSERT INTO tablename VALUES ('value1', ...);

However, there are several other methods as well. What method is used in you book?

Report message to a moderator

Re: how to insert multiple rows into table [message #183736 is a reply to message #183709]

Sat, 22 July 2006 17:25

vin_odks
Messages: 153
Registered: July 2006
Location: -

Senior Member

Hi,

Are you looking out for Parallel inserts ???

Regards
Vinod

Report message to a moderator

Re: how to insert multiple rows into table [message #183739 is a reply to message #183709]

Sun, 23 July 2006 02:44

Peter D.
Messages: 19
Registered: June 2006
Location: Warsaw, Poland

Junior Member

INSERT INTO <your_table>
SELECT <columns>
FROM <another_table>

Peter D.

[Updated on: Sun, 23 July 2006 02:45]

Report message to a moderator

Re: how to insert multiple rows into table [message #183744 is a reply to message #183739]

Sun, 23 July 2006 03:45

parmaleuday
Messages: 9
Registered: July 2006
Location: pune

Junior Member

hi friends

actually i m looking for parallel insert.
i want to insert multiple rows in a single insert command.

Report message to a moderator

parallel insert [message #183745 is a reply to message #183709]

Sun, 23 July 2006 03:46

parmaleuday
Messages: 9
Registered: July 2006
Location: pune

Junior Member

hi friends

i m looking for parallel insert.
i want to insert multiple rows in a single insert command.
how is it possible?

Report message to a moderator

Re: parallel insert [message #183758 is a reply to message #183745]

Sun, 23 July 2006 06:19

Frank Naude
Messages: 4581
Registered: April 1998

Senior Member

Something like this?

SQL> create table t1 (c1 number);

Table created.

SQL>
SQL> INSERT ALL
  2    INTO t1 VALUES (1)
  3    INTO t1 VALUES (2)
  4    INTO t1 VALUES (3)
  5    SELECT * FROM DUAL;

3 rows created.

Report message to a moderator

Re: how to insert multiple rows into table [message #183810 is a reply to message #183709]

Mon, 24 July 2006 00:59

pamrajrajeev
Messages: 9
Registered: July 2006

Junior Member

Hai

,

multiple rows can be inserted into a table like using single ampersand substitution(&) variable which accepts value at runtime by enclosing char and date values in sinle quote and numbers need not.

insert into table values(&var1,&var2,....);

eg : insert into own('raj','10-03-89',2500);

But from 9i, it is possible to insert multiple rows using single insert command.

Ragards

Rajeev

Report message to a moderator

Re: how to insert multiple rows into table [message #183816 is a reply to message #183810]

Mon, 24 July 2006 01:17

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

Senior Member

From 9i you can use Multitable inserts which are similar to the normal INSERT INTO...SELECT, but have more than one INTO clause.

"Parallel insert" is not the name of an Oracle feature in the documentation, although parallel DML is possible, so I'm not sure what you mean by that.

Perhaps rather than us prompting you with all the ways to insert a record until you see one you like, you tell us what you are trying to do and we help make it work.

btw substitution variables are a feature of SQL*Plus.

[Updated on: Mon, 24 July 2006 01:17]

Report message to a moderator

Re: how to insert multiple rows into table [message #183821 is a reply to message #183816]

Mon, 24 July 2006 01:24

Frank
Messages: 7901
Registered: March 2000

Senior Member

Topics merged.

Report message to a moderator

Re: parallel insert [message #183920 is a reply to message #183758]

Mon, 24 July 2006 06:59

parmaleuday
Messages: 9
Registered: July 2006
Location: pune

Junior Member

THANKS

i would also like 2 know how 2 use reference cursor in simpler way.

Report message to a moderator

Re: parallel insert [message #183925 is a reply to message #183920]

Mon, 24 July 2006 07:23

Frank
Messages: 7901
Registered: March 2000

Senior Member

Yes, you posted that already. Please stop posting the same questions in multiple threads.

By the way, please do not use IM speak ("2" instead of to)

Report message to a moderator

Re: parallel insert [message #183986 is a reply to message #183925]

Mon, 24 July 2006 13:02

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

Senior Member

I agree. There are only two letters in 'to' anyway, so what do you save?

Report message to a moderator

Re: parallel insert [message #183989 is a reply to message #183986]

Mon, 24 July 2006 13:36

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

You know, use smaller font to save disk space Smile

Report message to a moderator

Re: how to insert multiple rows into table [message #184952 is a reply to message #183744]

Fri, 28 July 2006 19:29

balaji1010
Messages: 35
Registered: March 2006
Location: London

Member

Just type in this insert command at SQL prompt

insert into <tablename> values(&colname, &colname...);

save <anyfilename>

every time you want to insert values into the table just run the file by command RUN <FILENAME>

every time you will be prompted with colnames asking you to enter the values.

you dont have to execute insert statement all the time.

thanx

Report message to a moderator

Re: how to insert multiple rows into table [message #184954 is a reply to message #184952]

Fri, 28 July 2006 19:31

balaji1010
Messages: 35
Registered: March 2006
Location: London

Member

sorry guys....

its a mistake

just ignore the message

i just messaged without looking at the previous once

i just went through the question and answered.

sorry once again

thanx

Report message to a moderator

Previous Topic:	Dynamicl DDL
Next Topic:	utl_mail.send to/from format/appearance

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 02 07:47:50 CST 2024