Solved

insert data into multiple tables using single stored procedure

Posted on 2009-05-18
7
2,139 Views
Last Modified: 2013-12-11
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

0
Comment
Question by:gpinfotech
  • 2
  • 2
  • 2
7 Comments
 
LVL 3

Accepted Solution

by:
jakemdrew earned 334 total points
ID: 24418665
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','');          
COMMIT;
0
 
LVL 3

Assisted Solution

by:jakemdrew
jakemdrew earned 334 total points
ID: 24418673
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:

INSERT ALL
   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')
SELECT * FROM dual;

This example will insert 2 rows into the suppliers table and 1 row into the customers table.
0
 
LVL 22

Expert Comment

by:Ivo Stoykov
ID: 24418732
Hi,
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
HTH
Ivo Stoykov
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:gpinfotech
ID: 24418740
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)
 
0
 

Author Comment

by:gpinfotech
ID: 24418778
Hello ivostoykov,

 create procedure pname
 as
begin

  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.
0
 
LVL 22

Assisted Solution

by:Ivo Stoykov
Ivo Stoykov earned 166 total points
ID: 24419452
you could just pass your query to SP and in there execute it and then do your inserts accordingly

HTH
Ivo Stoykov

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

Open in new window

0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Import and exporting Oracle Data with encrypted columns 4 26
Can > be used for a Text field 6 35
Oracle collections 15 16
error in my cursor 5 18
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now