By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,963 Members | 1,749 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,963 IT Pros & Developers. It's quick & easy.

Inserting multiple rows in sqlplus

P: n/a
Hi,

In sqlplus, I can insert a single row using:

insert into employee
(name, salary, hiredate)
values
('xyz', '86378', sysdate);

Is there a modification of the insert command that will allow me to
insert more than one row into a table without having to resort to
multiple "insert into ..." statements?

For example, can I insert two rows via something that lloks like:

insert into employee
(name, salary, hiredate)
values
{
('xyz', '86378', sysdate),
('abc', '84249', sysdate-1)
};

Here I am assuming that { } encloses all valid rows, with () enclosing
a single row within the surrounding {}.
I do not want to use the sql loader, PL-SQL, or SQL-PLUS constructs
like functions, scripts, and stored procedures.

Thanks
Raj
Jul 19 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Raj Kotaru wrote:
I do not want to use the sql loader, PL-SQL, or SQL-PLUS constructs
like functions, scripts, and stored procedures.

well, you're SOL then
Jul 19 '05 #2

P: n/a
Raj Kotaru wrote:
Hi,

In sqlplus, I can insert a single row using:

insert into employee
(name, salary, hiredate)
values
('xyz', '86378', sysdate);

Is there a modification of the insert command that will allow me to
insert more than one row into a table without having to resort to
multiple "insert into ..." statements?

For example, can I insert two rows via something that lloks like:

insert into employee
(name, salary, hiredate)
values
{
('xyz', '86378', sysdate),
('abc', '84249', sysdate-1)
};

Here I am assuming that { } encloses all valid rows, with () enclosing
a single row within the surrounding {}.
I do not want to use the sql loader, PL-SQL, or SQL-PLUS constructs
like functions, scripts, and stored procedures.

Thanks
Raj


INSERT INTO EMPLOYEE AS
SELECT A.NAME, B.SALARY, C.HIREDATE
FROM NAME_TABLE A, SALARY_TABLE B, HIREDATE_TABLE C
WHERE A.SSN = B.SSN
AND A.SSN = C.SSN;

Jul 19 '05 #3

P: n/a

"Raj Kotaru" <ra********@hotmail.com> wrote in message
news:67**************************@posting.google.c om...
Hi,

In sqlplus, I can insert a single row using:

insert into employee
(name, salary, hiredate)
values
('xyz', '86378', sysdate);

Is there a modification of the insert command that will allow me to
insert more than one row into a table without having to resort to
multiple "insert into ..." statements?

For example, can I insert two rows via something that lloks like:

insert into employee
(name, salary, hiredate)
values
{
('xyz', '86378', sysdate),
('abc', '84249', sysdate-1)
};

Here I am assuming that { } encloses all valid rows, with () enclosing
a single row within the surrounding {}.
I do not want to use the sql loader, PL-SQL, or SQL-PLUS constructs
like functions, scripts, and stored procedures.

Thanks
Raj


Not really. But you could create an external table (if you are using 9i)
and then do a select into.
Is this for school? SQLLoader does this by multiple insert statements and
bind variables.
Jim
Jul 19 '05 #4

P: n/a
Raj Kotaru wrote:

Hi,

In sqlplus, I can insert a single row using:

insert into employee
(name, salary, hiredate)
values
('xyz', '86378', sysdate);

Is there a modification of the insert command that will allow me to
insert more than one row into a table without having to resort to
multiple "insert into ..." statements?

For example, can I insert two rows via something that lloks like:

insert into employee
(name, salary, hiredate)
values
{
('xyz', '86378', sysdate),
('abc', '84249', sysdate-1)
};

Here I am assuming that { } encloses all valid rows, with () enclosing
a single row within the surrounding {}.

I do not want to use the sql loader, PL-SQL, or SQL-PLUS constructs
like functions, scripts, and stored procedures.

Thanks
Raj


Oracle does not support what you ask, and AFAIk neither does the SQL
standard. (Corrections appreciated if I'm wrong here.)

I'm not sure why *you* want this, although I can think of several
reasons. Nor do I know your environmnt, your Oracle version, your
operating system version, or your data source. I'd certianly be
interested in the reasoning behind your restrictions about SQL Loader,
etc.

Some alternate ways I've used to do something like this:

1) With a flat file and Oracle9i, consider External Tables;
2) If data is in the database already, use CTAS;
3) Create a SQL Plus script to contains all inserts. If possible
(incredibly easy in unix/linux), generate the script using sed, awk or
perl. Cut size by inserting against a view that maps only to the
columns you want, eliminating the column spec.

<rant>
While there may be legit reasons for the restrictions, you are
effectively increasing the cost of your Oracle investment. This is
similar to saying "though shalt not use the power seats, power windows
or radio in the car - the money that was spent on those features is
wasted."
</rant>
Jul 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.