how to insert the data in 2 tables in pl/sql stored procedure?

ramesh

i have 2 tables table1, table2 . table1 columns are id,empname and table2 columns are id,empname, location.. so first insert the data in table1 based on table1 columns id, empname insert the data in table2.. that means table2 id,empname columns data must be same to table1 columns...if insert unmatched data it has to through error example

table1 table2
id empname id empname location
10 raju 10 raju hyd
20 kumar 20 kumar kerala
50 anil 50 anil TN

will no insert like that table2
id empname location
20 raju hyd
11 kumar kerala
here if try to enter that type of data it has to show error

i want script in pl/sql stored procedure
please help me urgent......

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Shankar M14y ago

Hi Ramesh,

You can use INSERTALL.. FROM DUAL statement in your procedure to insert data to multiple tables.

Thanks,
Shankar
0
ramesh14y ago
please convert into plsql procedure not in sql server
0
Sukesh Marla14y ago
CREATE
@ID
PROCEDURE SprInsertToTable2int,
@EmployeeName
varchar(100),
@Location
varchar(1000)
AS
BEGIN

SET NOCOUNT ON;

if(Exists(select top 1 id from Table1 where ID=@ID and EmployeeName=@EmployeeName))

begin

Insert into Table2(Id,EmployeeName,Location) values(@Id,@EmployeeName,@Location)

end
END

Check
this is correct answer if it helped
0
ramesh14y ago
i want the script in plsql stored procedure only.... please help me
0
Sukesh Marla14y ago
Does Table 1 already fileld with some records?
and you want to cehck for existence before inserting into Table 2
Check this is correct answer if it helepd
0