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 4 75736
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
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;
"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
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> This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: RJ |
last post by:
We currently send product releases to our customers, and often have to
include scripts that need to be ran on the Oracle databases (also do
it for SqlServer customers, but we use a different set of...
|
by: tyousaf |
last post by:
Hi
i am new to mysql and mysql++, i have installed mysql server, it is
running fine. i also installed "mysql++-1.7.9gcc3.2-2.i386.rpm" (i
have gcc 3.3) , first of all as the readme file says to do...
|
by: JB |
last post by:
To anyone that is able to help....
What I am trying to do is this. I have two tables (Orders, and
OrderDetails), and my question is on the order details. I would like
to set up a stored...
|
by: Scott Chapman |
last post by:
I am working with Python (psycopg). I have HTML with embedded Python that I'm
inserting into a database and it could contain any character.
Single quotes, at least, must be escaped (to two...
|
by: Arsen V. |
last post by:
Hello,
What is the optimal way to insert multiple rows (around 1000) from a web
application into a table?
The user enters multiple lines into a text box (up to 10,000). The ASP.NET...
|
by: talktozee |
last post by:
Hey, everyone!
Basically, I need to insert *multiple rows* into table A from table B based upon some criteria, and I need to insert some static values along with each row from table A.
For...
|
by: nunrgleo |
last post by:
Hi all,
I need to write a script that logs into several oracle databases in a sequential manner from a unix box using different login and password e.g sqlplus found/duckl@dnd007 and many more...
|
by: Raj Kotaru |
last post by:
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...
|
by: Eraser |
last post by:
Hello,
I'm just starting to learn PL/SQL. To get my feet wet,
I'm trying to write a simple stored procedure that takes some
values as parameters, and inserts those values into a table.
For...
|
by: lllomh |
last post by:
Define the method first
this.state = {
buttonBackgroundColor: 'green',
isBlinking: false, // A new status is added to identify whether the button is blinking or not
}
autoStart=()=>{
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: Aliciasmith |
last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
|
by: giovanniandrean |
last post by:
The energy model is structured as follows and uses excel sheets to give input data:
1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
|
by: NeoPa |
last post by:
Introduction
For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM)
Please note that the UK and Europe revert to winter time on...
|
by: nia12 |
last post by:
Hi there,
I am very new to Access so apologies if any of this is obvious/not clear.
I am creating a data collection tool for health care employees to complete. It consists of a number of...
|
by: isladogs |
last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, Mike...
|
by: GKJR |
last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...
| |