I need to copy some selected records from multiple tables.
To determine the records to copy, I would use MINUS of two SELECTs like this:
Select * Into NestedTable From (
SELECT v_Fields FROM v_SourceTable
MINUS
Select v_Fields FROM v_TargetTable
Sort by v_Fields);
v_Fields, v_SourceTable, and v_TargetTable are all variables. Before running the MINUS script, I set them up by a configuration table, because the next time the code is run, it copies other fields with other tables.
The next step is to call a LOOP on the NestedTable to copy each record and report whether the copy was successful or not.
It’s also perfect for me if the result of MINUS goes into a cursor and I call the copy method from the cursor.
Haw can I write this code? Thanks in advance!
I made my merge version. Thanks Alex! But I don’t know how to log when a copy of a record fails. This code is fine for the parent table, but I need to copy the child tables as well. The children are connected to the parent by a few dedicated fields. How can I insert the code to copy the child tables into this?
Declare
-- All default values will be method parameter in the final version
v_SourceTable Varchar2(100) Default 'MergeTestVals';
v_TargetTable Varchar2(100) Default 'MergeTestIns';
v_ConfFields Varchar2(100) Default 'Field1,Field2,Filed3,Field4';
v_SqlStmt Varchar2(2000);
v_OnFields Varchar2(1000);
v_InsertFields Varchar2(1000);
v_ValueFields Varchar2(1000);
Begin
For i In (Select Trim(Regexp_Substr(v_ConfFields,'[^,]+',1,Level)) Field
From Dual
Connect By Level <= Length(v_ConfFields)-Length(Replace(v_ConfFields,','))+1)
Loop
v_OnFields := v_OnFields||'it.'||i.Field||'=st.'||i.Field||' And ';
v_InsertFields := v_InsertFields||'it.'||i.Field||',';
v_ValueFields := v_ValueFields||'st.'||i.Field||',';
End Loop;
v_OnFields := Rtrim(v_OnFields,' And ');
v_InsertFields := Rtrim(v_InsertFields,',');
v_ValueFields := Rtrim(v_ValueFields,',');
v_SqlStmt := 'Merge Into '||v_TargetTable||' it '||
'Using '||v_SourceTable||' st '||
'On ('||v_OnFields||')'||
'When NOT Matched Then Insert ('||v_InsertFields||') '||
'Values ('||v_ValueFields||')';
Execute IMMEDIATE v_sqlstmt;
Dbms_Output.Put_Line(SQL%RowCount);
End;
2
You need a procedure with parameters to create and execute dynamic commands:
Create Or Replace Procedure
add_missing_rows(p_src_table Varchar2, p_src_cols Varchar2, p_tgt_table Varchar2, p_tgt_cols Varchar2) IS
BEGIN
Declare
v_cnt Number(6) := 0;
v_sql Varchar2(4000) := 'SELECT Count(*) FROM (' ||
'Select ' || p_src_cols || ' From ' || p_src_table || Chr(10) ||
'MINUS' || Chr(10) ||
'Select ' || p_tgt_cols || ' From ' || p_tgt_table || ')';
v_sql_ins Varchar2(4000) := 'INSERT INTO ' || p_tgt_table || Chr(10) ||
'Select ' || p_src_cols || ' From ' || p_src_table || Chr(10) ||
'MINUS' || Chr(10) ||
'Select ' || p_tgt_cols || ' From ' || p_tgt_table;
Begin
Execute Immediate v_sql Into v_cnt;
If v_cnt > 0 Then
Execute Immediate v_sql_ins;
Commit;
End If;
dbms_output.put_line('Number of rows commited: ' || To_Char(v_cnt));
End;
END add_missing_rows;
… if we create some test data like:
-- S a m p l e D a t a :
Create Table src_1 ( id Number(3), src_fld1 Varchar2(6), src_fld2 VarChar2(6), src_fld3 Varchar2(6) );
Insert Into src_1 Select 1, 'A', 'B', 'C' From Dual;
Create Table tgt_1 ( id Number(3), tgt_fld1 Varchar2(6), tgt_fld2 VarChar2(6), tgt_fld3 Varchar2(6) );
Create Table src_2 ( id Number(3), src_col1 Varchar2(6), src_col2 VarChar2(6), src_col3 Varchar2(6) );
Insert Into src_2 Select 1, 'D', 'E', 'F' From Dual;
Create Table tgt_2 ( id Number(3), tgt_col1 Varchar2(6), tgt_col2 VarChar2(6), tgt_col3 Varchar2(6) );
… then we can run the procedure with different tables involved and differernt lists of columns from those tables.
SET SERVEROUTPUT ON
BEGIN
add_missing_rows('src_1', 'id, src_fld1, src_fld2, src_fld3', 'tgt_1', 'id, tgt_fld1, tgt_fld2, tgt_fld3');
END;
/
… or some other pair …
SET SERVEROUTPUT ON
BEGIN
add_missing_rows('src_2', 'id, src_col1, src_col2, src_col3', 'tgt_2', 'id, tgt_col1, tgt_col2, tgt_col3');
END;
/
-- R e s u l t :
Number of rows commited: 1