• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2158
  • Last Modified:

insert data into multiple tables using single stored procedure

Hi All,
  I need to build a stored procedure to insert data into different tables of a database.
  Here each table is different from other in the concept of number of columns and data types.
Here i want to  put different insertion statements in one single stored procedure.
so just by executing this stored procedure once, i want to  insert data into one table based on the given table_name as input parameter to the store procedure.

Is this possible to develop a stored procedure like this?.
If possible ,Pls give me the syntax of the SP.
Thanks in Advance

  • 2
  • 2
  • 2
3 Solutions
Just seperate each individual insert statment with a semi colon.  Does not matter how many or what they look like.

INSERT INTO CUSTOMERS VALUES ('230','Andrews','Rebecca','F','12-MAR-69','19-FEB-95','A',
'1456 Sycamore St','Falls Church','VA','22040','7038661521','7053567712','Y','','','');                        
INSERT INTO CUSTOMERS VALUES ('326','Milton','Henry','M','21-SEP-53','12-MAY-95','A',
'1723 Marshall Lane','Arlington','VA','22019','3019522214','7037213725','Y','Y','','');                            
INSERT INTO CUSTOMERS VALUES ('327','Jackson','Cindy','F','31-OCT-43','30-AUG-94','A',
'123 Courthouse Road','Fairfax','VA','22102','7034454454','7038547854','Y','','','Y');                            
INSERT INTO CUSTOMERS VALUES ('329','Jeffrey','Denning','M','15-MAR-64','19-FEB-96','A',
'1721 Whitney Drive','Falls Church','VA','22040','7036547896','7055435712','Y','','','');                      
INSERT INTO CUSTOMERS VALUES ('330','Mayer','Arthur','M','11-JUN-73','12-MAY-96','A',
'729 Castle Ct','Reston','VA','22019','7038246545','7037213324','','','Y','Y');                                    
INSERT INTO CUSTOMERS VALUES ('331','Baker','Paul','M','19-DEC-69','30-SEP-95','A',
'1423 North 15th Rd','Arlington','MD','22102','7034257894','7038542154','','Y','','');                              
INSERT INTO CUSTOMERS VALUES ('332','Johnson','Michelle','F','21-MAR-66','20-APR-95','A',
'123 North Main Street Apt. 309','Falls Church','MD','20129','7035643215','7038659856','','','Y','');          
You can also insert multiple rows into multiple tables. For example, if you wanted to insert into both the suppliers and customers table, you could run the following SQL statement:

   INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
   INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
   INTO customers (customer_id, customer_name, city) VALUES (999999, 'Anderson Construction', 'New York')

This example will insert 2 rows into the suppliers table and 1 row into the customers table.
Ivo StoykovCommented:
Yes, it is possible, but I doubt one could give you a sample without knowing the loginc, conditions and so on.
Here is a good starting point -> http://download.oracle.com/docs/html/A95261_01/jdgtut1.htm
Ivo Stoykov
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

gpinfotechAuthor Commented:
But i need to pass each row as input parameter.
How to pass values as input parameters to different input statements for different tables.

insert into table1(....)values(val1,val2)

insert into table2(-,-) values(valx,valy)
gpinfotechAuthor Commented:
Hello ivostoykov,

 create procedure pname

  inseret into table1(-,-) values(-,-)

  insert into table2(-,-) values(-,-)

  insert into table3(-,-) values(-,-)
------lke this stored procedures contains different insertion statements.
by executing SP i want to run only one insert statement out of many.And i want to pass values as input parameters.So based on the input parameters ,particular input statement should execute.
Ivo StoykovCommented:
you could just pass your query to SP and in there execute it and then do your inserts accordingly

Ivo Stoykov

create or replace procedure _test(q in Varchar2) is
r  your_table%ROWTYPE;
OPEN p_cursor FOR q
    USING ...; -- params here
    FETCH p_cursor INTO r;
    EXIT WHEN p_cursor%NOTFOUND;
    if ... then
      inseret into table1(-,-) values(-,-);
    end if;
 CLOSE p_cursor;

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now