Is this possible in Data Manager?

[verocarbelo]

Hi,

I would like to do this in Data Manager:

I have data divided pro year in different tables (table_name_2008,table_name_2009...).
The new data come from a csv file. This file contains the same attributes plus a new one with the year
(attribut1,attribute2,...,year(2009)).
I must delete and insert the new data in the right table (the right year).
How can I do this in data Manager?

thanks in advance.

[SSNCOG]

Hi,

does the csv file has data for all years or just has 2009?
Do you want to move year(2009) attribute to the target table as well?

SSNCOG

[verocarbelo]

Hi,

does the csv file has data for all years or just has 2009?
Do you want to move year(2009) attribute to the target table as well?

SSNCOG

The csv file has data for one year. I don´t need the attribute year any more.
I hope you can help me.
Thank you

[verocarbelo]

I have done it with a PL/SQL script.
Can I integrate it in a Data Manager Job?THANKS FOR YOUR HELP :)
The script is:

declare
v_tablename varchar2(100);
v_del_statement varchar(1000);
v_ins_statement_1 varchar(1000);
v_ins_statement_2 varchar(1000);

cursor c_jahr is select distinct jahr from soldwh.PIVOTING4_KART4;

begin

dbms_output.put_line ('Start');

for r_jahr in c_jahr loop

v_tablename:= 'soldwh.sol_f_kore_plan_'||to_char(r_jahr.jahr);

--zum testen output des strings
dbms_output.put_line (v_tablename);

v_del_statement := 'DELETE FROM '|| v_tablename || ' WHERE EXISTS ( select * '
|| ' from soldwh.pivoting5_KART4 , ' || v_tablename
|| ' where pivoting5_kart4.KOSTENART_KEY = ' || v_tablename ||'.KOSTENART_KEY '
|| ' and pivoting5_kart4.KOSTENSTELLE_KEY = ' || v_tablename ||'.KOSTENSTELLE_KEY '
|| ' and pivoting5_kart4.PERIODE_KEY = ' || v_tablename|| '.PERIODE_KEY'
|| ' and pivoting5_kart4.MANDANT_KEY = ' || v_tablename|| '.MANDANT_KEY)';

--zum testen output des strings
dbms_output.put_line (v_del_statement);
EXECUTE IMMEDIATE (v_del_statement);

v_ins_statement_1 := 'Insert into ' || v_tablename
|| ' (PERIODE_KEY,BUCHUNG_BETRAG,WAEHRUNG,MANDANT_KEY,KOSTENSTELLE_KEY,KOSTENART_KEY) '
|| ' SELECT PERIODE_KEY,BUCHUNG_BETRAG,WAEHRUNG,MANDANT_KEY,KOSTENSTELLE_KEY,KOSTENART_KEY FROM soldwh.pivoting5_KART4';

--zum testen output des strings:

dbms_output.put_line(v_ins_statement_1);

EXECUTE IMMEDIATE  (v_ins_statement_1);


commit;

dbms_output.put_line ('Ende');
end loop;

end;