I wanted to know how we can move table data to another table using stored procedure

Albert Chao

Suppose there is one table Employees with the data

create table Employees (emp_id number, emp_name varchar2(50), salary number, department_id number, DESIGNATION varchar2(50), DEVELOPED_TESTED varchar2(50)) ;

insert into Employees values(1,'ALex',10000,10,'Developer','VLC');
insert into Employees values(2,'Duplex',20000,20,'Developer','VLC');
insert into Employees values(3,'Charles',30000,30,'Tester','Tested_VLC');
insert into Employees values(4,'Demon',40000,40,'Tester	','Tested_VLC');

select * from employees;

Now, using stored procedure I want to move Employees table data into another table say Department using the stored procedure. If you have such an example kindly share your thoughts on this. Create a procedure to move the data to another table will be more helpful if you can apply some conditions or filters. Just I want to learn how we can do this using stored procedure. Would appreciate it if you can share any example like this wherein we are applying some logic or filters then inserting them into another table?

Solomon Yakobson

It doesn't make much sense. Normally employee table would have foreign key to deparment table, so there is nothing to move. And if there isn't such FK then all we have is department id and not department name, location, etc.

SY.

mathguy

Cross-posted: https://stackoverflow.com/questions/69212569/how-to-move-one-table-data-to-another-table-using-stored-procedure

User_H3J7U

Just I want to learn how we can do this using stored procedure.

PL/SQL Language Reference

Jan Gorkow

Hi @Albert Chao ,

I would do it like this for small tables. The procedure moves the rows from table employees to table employees2 and it does this within its own transaction (pragma autonomous_transaction):

CREATE TABLE employees2
(
  emp_id       NUMBER,
  emp_name      VARCHAR2 (50 CHAR),
  salary       NUMBER,
  department_id    NUMBER,
  designation     VARCHAR2 (50 CHAR),
  developed_tested  VARCHAR2 (50 CHAR)
);

CREATE OR REPLACE PROCEDURE prc_move_employees_data
IS
  PRAGMA AUTONOMOUS_TRANSACTION;

  CURSOR c_employees IS
   SELECT employees.emp_id,
       employees.emp_name,
       employees.salary,
       employees.department_id,
       employees.designation,
       employees.developed_tested
    FROM employees
   FOR UPDATE;
BEGIN
  FOR v_employee IN c_employees
  LOOP
   INSERT INTO employees2 (employees2.emp_id,
               employees2.emp_name,
               employees2.salary,
               employees2.department_id,
               employees2.designation,
               employees2.developed_tested)
      VALUES (v_employee.emp_id,
          v_employee.emp_name,
          v_employee.salary,
          v_employee.department_id,
          v_employee.designation,
          v_employee.developed_tested);

   DELETE FROM employees
      WHERE CURRENT OF c_employees;
  END LOOP;

  COMMIT;
END prc_move_employees_data;
/

Best regards

Jan

BluShadow

https://community.oracle.com/tech/developers/discussion/comment/16809328#Comment_16809328

Why use cursor loops?

Why not just:

CREATE OR REPLACE PROCEDURE prc_move_employees_data IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  insert into employees2
    select * from employees;
  delete from employees
    where emp_id in (select emp_id from employees2);
  commit;
END prc_move_employees_data;
/

NextName

Hi BluShadow,

which begs the question why not doing it in pure SQL from the beginning.

I'm curious: what would PRAGMA AUTONOMOUS_TRANSACTION add in your case?

Regards, NextName

Jan Gorkow

https://community.oracle.com/tech/developers/discussion/comment/16809341#Comment_16809341

Hi @BluShadow ,

in your solution there is a little chance of lost updates. Consider the following:

Someone starts your prc_move_employees_data and the insert statement begins to copy the data. While the insert runs, someone in another session may update a record in employees table and commit it. The insert finishes and your delete starts. It "deletes" the new version of the meanwhile updated row. Thats why I would lock the rows to move with the for update clause of the cursor. Another option would be an exclusive table lock in your procedure.

Best regards

Jan

BluShadow

https://community.oracle.com/tech/developers/discussion/comment/16809495#Comment_16809495

Me personally, I wouldn't use autonomous transaction for it. That was left over from Jan's code. ;)

User_H3J7U

Instead of select + insert + delete , it would be more effective to delete first, then bulk insert.