Interested to Buy Any Domain ? << Click Here >> for more details...
Ho to insert no. of records at a time..i mean i want to
insert 100 records at a time into a table
- 5 Answers
- 10686 Views
- I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / swapna
Example there is a table name emp.It has 100 records
Another table name is dept
Now i want to insert table emp 100 records into dept
--insert into dept select * from emp
| Is This Answer Correct ? | 3 Yes | 0 No |
Answer / ron
i assume you are doing batch processing (say processing of
millions of rows):
1. add a new column to the table say t2 called
processed_rows char(1) not null default of 'N'
2. in a pl/sql block (start loop);
update processed columns to 'P' for processing
where rownum<101;
2. if rowcount after update=0 exit loop.
3. next insert into t1 select from t2 where processed_rows='P'
4. update t2 change column from 'P' to 'Y (saying processed)
6 commit;
7. loop until all rows are processed.
8. advantages. you can restart the process after failure.
and done have to start all over again only those that that
have not been processed will be processed.
to improve performance is millions of rows are being
processed then:
partition the table table;
and run the above sql against each individual partitions;
processing will be done that many times faster.
good luck!
| Is This Answer Correct ? | 2 Yes | 0 No |
In oracle 10g :
---------------->
Step 1 : SQL> CREATE TABLE identity( ID NUMBER);
Step2 : Write a Simple Procedure for to insert values into
table.
DECLARE
i NUMBER;
BEGIN
FOR i IN 1..100 LOOP
INSERT INTO identity VALUES(i);
END LOOP;
END;
/
PL/SQL procedure successfully completed.
Step 3 : SELECT COUNT(*) FROM identity;
COUNT(*)
----------
100
Please check it. Works fine.
PL/SQL procedure successfully completed.
| Is This Answer Correct ? | 0 Yes | 0 No |
Answer / raghavendra
insert into table 1 value (select * from table2 where
rownum<101 )
| Is This Answer Correct ? | 2 Yes | 4 No |
What is a Garbage Collection? and what is full recursive Garbage collection?
when do u go for hash partitioning?
How to list all indexes in your schema?
Why we use bulk collect in oracle?
What is TNS File
How do you rate yourself in oracle and sql server ?
can anyone help me ? an index has been done on the primary key of a table. an update operation was performed on that table. now my question is 1> what abt the performance ? means faster or slower due to indexing ? 2> does the operation affect to the primary key constraint ?
How to write a query with a full outer join in oracle?
what is the output of select * from emp where null=null & select * from emp where 1=1
i have a table with 1 lac of data.i want to insert this data into another table. i want every 5000 of data insert use a commit.using counter and if error is comming in the 5000 of data it rollback.
How to retrieve data from an explicit cursor?
how to find out largest value in a table
Oracle General 
