asked on

PL/SQL help needed. Script to compare columns and copy data between tables

I need assistance with PL/SQL. My experience is limited. My insert statement below I dont believe is in correct syntax.

The goal:
I'm copying data from one Oracle table (a custom interface table) to another Oracle table, both owned by same schema with a PL/SQL script.

Details:

Tables involved including alias:

ARMS_TO_PRIM_HOURS_INTERFACE A
Active_Projects P
Activity ACT

Compare A & P and write to ACT table:

A.Project_Number = P.PROJECT
and A.Task_Number = P.Path
Those that have a match, copy A.Hours and insert into Act.Actuallaborunits
Where P.ObjectID = Act.projectobjectID

SQL Code with full names:

set define off;
set serveroutput on;
declare
  l_error varchar2(4094);
begin
  dbms_output.put_line('Start of script named XXX');

  --Select/Insert statements here

Insert ARMS_TO_PRIM_HOURS_INTERFACE.HOURS into ACTIVITY.Actuallaborunits
Where ARMS_TO_PRIM_HOURS_INTERFACE.Project_Number = ACTIVE_PROJECTS.Project
and ARMS_TO_PRIM_HOURS_INTERFACE.Task_Number = ACTIVE_PROJECTS.Path
and ACTIVE_PROJECTS.ObjectID = ACTIVITY.projectobjectID

  --commit

  dbms_output.put_line('End of script named XXX');
EXCEPTION WHEN OTHERS THEN
  l_error := NVL(SUBSTR(SQLERRM,0,990),'NULL');
  dbms_output.put_line(l_error || ':-' || NVL(SUBSTR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,0,3000),'NULL')); 
  rollback;
end;

Open in new window

Sean Stuber

there is no where clause for an insert
you simply select (the sql below is just for illustration of basic structure, it is not expected to be a final solution)

DECLARE
l_error VARCHAR2(4094);
BEGIN
DBMS_OUTPUT.put_line('Start of script named XXX');

--Select/Insert statements here

INSERT INTO activity
SELECT * FROM arms_to_prim_hours_interface;

--commit

DBMS_OUTPUT.put_line('End of script named XXX');
EXCEPTION
WHEN OTHERS
THEN
l_error := NVL(SUBSTR(SQLERRM, 0, 990), 'NULL');
DBMS_OUTPUT.put_line(
l_error || ':-' || NVL(SUBSTR(DBMS_UTILITY.format_error_backtrace, 0, 3000), 'NULL')
);
ROLLBACK;
END;

Sean Stuber

but...

I think maybe what you wanted was an update, not an insert, or maybe a merge.

provide sample data for all tables and expected results

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

k3vsmith

ASKER

Thanks for your responses! Yes you are correct. I want to do an update, not familiar with merge but will look that up. Basically all actuallaborunits are currently 0. I want to update them to the value of HOURS from the ARMS_TO_PRIM_HOURS_INTERFACE table based on project and Path. Ill update this with some data from each to give you a better example. Ill also play around with what you've already provided.

Thanks

k3vsmith

ASKER

So I have the select statement giving me the data I want. See attached excel output with sql statement on second tab. Ive been reading around. Do I want to put this select statement into a cursor and the use some kind of loop to write an update statement to update act.actuallaborunits?
test.xls

Sean Stuber

>>>Do I want to put this select statement into a cursor and the use some kind of loop to write an update

probably not, looping in a cursor would be inefficient when a single sql should suffice.
Instead you'll want to modify the merge above to have a different USING subquery
and then a different ON condition

USING - is the set of data you're using as the source
ON - is the condition used to link your source data to your target data.