Topic: SQL*Plus and PL/SQL >> query/script to insert multiple rows in a table
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Title: query/script to insert multiple rows in a table |
| vikhardba |
Posted: Jun 26, 2008 02:23:23 AM |
Total Post: 86
Joined: Apr, 2007
|
Dear All,
Oracle 10g
Windows XP
Can you please provide me the query/script to insert multiple rows in a table.
For example:-
insert into defect_code (defect_code,desc,defect_cls) value ('S1','abc','A');
insert into defect_code (defect_code,desc,defect_cls) value ('S2','ash','A');
If i have rows in 2 digit i can use above statement but what if i have 1000 rows i.e; from S1 - S1000
Regards,
Vikhar. |
|
|
 shake |
| Posted: Jun 26, 2008 03:08:27 AM |  |
|
Total Post: 84
Joined: Jun, 2008
|
Try with procedures.
CREATE OR REPLACE PROCEDURE ADD_DEFECT_CODE(d_code IN varchar2, des IN varchar2, d_cls IN vrachar2) IS
BEGIN
INSERT INTO DEFECT_CODE(defect_code,desc,defect_cls) VALUES (d_code, des, d_cls);
END;
To execute it,
EXEC ADD_DEFECT_CODE('S1','abc','A');
EXEC ADD_DEFECT_CODE('S2','ash','B');
You can't make all inserts at one shot, coz all records differ distinctly.
Your table usually get bulk of data through the front-end application. So, probably you have less situations to insert 1000s of record through your back-end.
|
|
|
|
|
| vikhardba |
| Posted: Jun 26, 2008 04:35:53 AM | |
|
Total Post: 86
Joined: Apr, 2007
|
Thanks for your speedy response. I've tried the CREATE PROCEDURE and it is working perfectly, thanks a lot. Yes, i agree with you that v get less situations to insert loads of records through back-end. However, we should have something like
Enter defect_code for row1:
Enter desc for row1:
Enter defect_class for row1:
Once you finish entering row1 data, it should ask for row2
Enter defect_code for row2: and so on
|
|
|
|
|
| shake |
| Posted: Jun 26, 2008 07:14:20 AM | |
|
Total Post: 84
Joined: Jun, 2008
|
For the similar kind of job, you can try this too.
DECLARE
col dbms_output.chararr;
j integer := 0;
m integer := 2; -- m -> No.of records
n integer := 3; -- n -> No.of fields
BEGIN
-- First Record
col(0) := 'S1';
col(1) := 'abc';
col(2) := 'A';
-- Second Record
col(3) := 'S2';
col(4) := 'efg';
col(5) := 'B';
FOR i IN 1 .. n LOOP
insert into defect_code (defect_code,desc,defect_cls) value (col(j),col(j+1),col(j+2));
j := j + n;
END LOOP;
END;
/
I'm sorry not to provide what you need. I am unaware of user interactive input in SQL as you asked in your reply.
Plz anyone share your ideas if you've got an opportunity to work with the same situation or you have an answer.
|
|
|
|
|
| shake |
| Posted: Jun 26, 2008 11:15:28 AM | |
|
Total Post: 84
Joined: Jun, 2008
|
To make it simpler, try this:
SQL>INSERT INTO defect_code(defect_code,desc,defect_cls) VALUES (&def_code,&desc,&def_cls);
Enter value for def_code:'S1'
Enter value for desc:'abc'
Enter value for def_cls:'A'
1 row created.
SQL>/
Enter value for def_code:'S2'
Enter value for desc:'def'
Enter value for def_cls:'B'
1 row created.
and so on.
Use the / operator each time to get the previous query repeated.
This is how you can do your multiple insertion of data without repeating the INSERT query each time.
Instead, you are prompted with a message and asked an input with the variable name you have given along with the & operator(temporary substitution operator).
|
|
|
|
|
| vikhardba |
| Posted: Jun 28, 2008 01:49:55 AM | |
|
Total Post: 86
Joined: Apr, 2007
|
Thanks for your response. Yes, i was looking for your last solution.
INSERT INTO defect_code(defect_code,description,defect_class) VALUES ('&defect_code','&desc','&defect_class');
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 857 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Gopu Gopi | 334 |
| Gitesh Trivedi | 322 |
| Vinoth Kumar | 264 |
| neeraj sharma | 258 |
| Ramesh Jois | 246 |
|
|
