Procedure to insert data from one column into two columns in another table

Question

I'm trying to get a procedure that will allow me to get data from a column and insert it into two different columns in a different table. the first table currently has both first and last name in a single column. I have another table with first name and last name in different columns and I need to separate and insert them from Column1/Table1 into the two columns in Table2 preferably using a procedure since I have a lot of names to migrate.

Column1(Name) in Table 1 looks like this

     NAME          
First_Name1 Last_name1 

First_Name2 Last_Name2 

First_Name3 Last_Name3       

And I need the data to be separated like this in Table2 as FName/LName using the data from the first table:

F_Name     |  L_Name

First_Name1|Last_Name1

First_Name2|Last_Name2

First_Name3|Last_Name3

I figured out how to get the data from the last and the first name separated using SUBSTR and INSTR, but I can't figure out how to put this inside a Procedure, or how to Loop it since I want to use it for several rows.

select substr(staff.name, 0, instr(staff.name, ' ')-1) as Fname
from staff;
select substr(staff.name, instr(staff.name,' ')+1) as Lname
from Staff;

Any ideas/Help? Thanks guys.

Answer 1

Building a Looping PL/SQL Based DML Cursor For Multiple DML Targets

A PL/SQL Stored Procedure is a great way to accomplish your task. An alternate approach to breaking down your single name field into FIRST NAME and LAST NAME components could be to use an Oracle Regular Expression, as in:

SELECT REGEXP_SUBSTR('MYFIRST MYLAST','[^ ]+', 1, 1) from dual
-- Result: MYFIRST

SELECT REGEXP_SUBSTR('MYFIRST MYLAST','[^ ]+', 1, 2) from dual
-- Result: MYLAST

A procedure based approach is a good idea; first wrap this query into a cursor definition. Integrate the cursor within a complete PL/SQL stored procedure DDL script.

CREATE or REPLACE PROCEDURE PROC_MYNAME_IMPORT IS

    -- Queries parsed name values from STAFF (the source) table 

    CURSOR name_cursor IS
       SELECT REGEXP_SUBSTR(staff.name,...) as FirstName,
              REGEXP_SUBSTR(... ) as LastName
         FROM STAFF;

    BEGIN

       FOR i IN name_cursor LOOP
          
          --DML Command 1:
          INSERT INTO Table_One ( first_name, last_name )
          VALUES (i.FirstName, i.LastName);
          COMMIT;

          --DML Command 2:
          INSERT INTO Table_Two ...
          COMMIT;

          END LOOP;

    END proc_myname_import;

As you can see from the example block, a long series of DML statements can take place (not just two) for a given cursor record and its values as it is handled by each loop iteration. Each field may be referenced by the name assigned to them within the cursor SQL statement. There is a '.' (dot) notation where the handle assigned to the cursor call is the prefix, as in:

CURSOR c1 IS
   SELECT st.col1, st.col2, st.col3
     FROM sample_table st
    WHERE ...

Then the cursor call for looping through the main record set:

FOR my_personal_loop IN c1 LOOP
    ...do this
    ...do that

    INSERT INTO some_other_table (column_one, column_two, column_three)
    VALUES (my_personal_loop.col1, my_personal_loop.col2, ...);

    COMMIT;
END LOOP;

... and so on.

Answer 2

This should work for you.

insert into newtable(FirstName, LastName)
  select substr(staff.name, 0, instr(staff.name, ' ') - 1),
         substr(staff.name, instr(staff.name, ' ') + 1)
    from staff;