Tuesday, March 25, 2008

How to insert data into mulitple tables in a single statement

In one of my projects, i had a requirement to insert data into more than one table, as i dint get a chance to get more experience in pl/sql, so i asked some of my colleagues "can we insert data into more than one table" everybody said we cannot.
I just don't want to write a procedure for this and i want to insert data into more than one tables. so i cannot use the below example as well because, with this you can insert into a single table only
insert into table_name values (select * from table_name)
I just thought googling to get some answer, i luckily found some forums which was explaining the "INSERT ALL " concept, with this concept you can insert into more than one table.
The below example will give you clear idea about the "INSERT ALL" concept.
Example:
1. Create a table A and B
2. Insert data into A and B in a single statement
create table tablea (sno number, name varchar2(20));
create table tableb (sno number, name varchar2(20));
3. insert all
into tablea(name) values ('THIS IS TABLE A')
into tableb(sno, name) values ('1','THIS IS TABLE B')
select 1 from dual;
4. select * from tablea;
Name
----------
THIS IS TABLE A
5. select * from tableb;
sno Name
----------------
1 THIS IS TABLE B
This is a new concept comes up with the 10g (10.1.2) feature. Make use and Enjoy.
I got the above information from a forum answered by Nicolas.Gasparotto.
Thanks Nicolas.

No comments:

Post a Comment