How to insert thousands of records in a table using procedure

This document describes how to insert thousands of records in a table using procedure.

Step 1: Create a test table

SQL> show user
USER is "VIJAY"


SQL> create table  test
  2  (SNUMBER number(10),
  3  Name varchar2(10)
  4  )
  5  ;

Table created.

SQL> insert into test values
  2  (100,'Vijay');

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from test;

  COUNT(*)
----------
       1

Step 2: Create a procedure using "for" loop to insert one crore values.

[oracle@server1 scripts]$ ls -ltr
total 4
-rwxrwxr-x. 1 oracle dba 103 Oct  9 17:58 insert_test.sql

[oracle@server1 scripts]$ cat insert_test.sql
declare
begin
for r in 1..10000000
loop
insert into test values(101,'Kumar');
end loop;
commit;
end;
/

Step 3: Execute the procedure to insert data

SQL> conn vijay/vijay
Connected.

SQL> @insert_test.sql

PL/SQL procedure successfully completed.

SQL>




Thanks for your patience to view this post.......