OraFAQ Forum: Server Utilities » sqlload - 2nd file with updating information

Home » RDBMS Server » Server Utilities » sqlload - 2nd file with updating information (Oracle 11)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sqlload - 2nd file with updating information [message #530253]

Mon, 07 November 2011 06:35

strait
Messages: 3
Registered: November 2011

Junior Member

Hello everybody...

I'm an absolut Oracle beginner and have problems with loading csv files into an Oracle DB.

I get 2 files in the morning, the first one has provisionally information like that:

03.11.2011;BFS;03:00:00;00:29:25;0,00572090;0,22408220;56,71504000;
03.11.2011;BFS;03:30:28;00:30:29;0,00676938;0,29812120;55,06820000;
03.11.2011;BFS;04:04:56;00:28:54;0,00315933;0,15332050;61,68312000;
03.11.2011;BFS;04:33:50;00:59:33;0,00093421;0,04295300;59,57108000;

I load them with sqlload into my table. Table keys are the frist three fields. Now in the second file there are updates in the numbers, f.e. like that:

03.11.2011;BFS;03:00:00;00:27:25;0,00572090;0,22408220;56,71504000;
03.11.2011;BFS;03:30:28;00:31:29;0,00676938;0,29812120;55,06820000;
03.11.2011;BFS;04:04:56;00:29:54;0,00315933;0,15332050;61,68312000;
03.11.2011;BFS;04:33:50;00:60:33;0,00093421;0,04295300;59,57108000;

I can't load the with sqlload, because the program says, the key already exists. My control file looks like that:

load data
infile 'example.csv'
badfile 'example.bad'
append
into table mf_sendung
fields terminated by ';' optionally enclosed by '"'
(
DATUM "TO_DATE(:DATUM,'DD.MM.YYYY')", PROG,
STARTZEIT "TO_TIMESTAMP(:STARTZEIT,'HH24:MI:SS')",
DAUER "TO_TIMESTAMP(:DAUER,'HH24:MI:SS')",
ZGESMIO, ZGESMA, ZGESSA,
)

Can anybody help me pleaaase.....

Confused

Report message to a moderator

Re: sqlload - 2nd file with updating information [message #530255 is a reply to message #530253]

Mon, 07 November 2011 06:42

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Don't APPEND; REPLACE (of course, if you can afford it).

Otherwise, if that table contains records from previous files (for example, yesterday's, last year's, ...), you'd first have to remove data that belongs to the file you got "this morning" and then APPEND the fresh data (from the most recent file).

Moreover, if possible, switch to external tables feature which allows you to update target table, based on data stored in a CSV file (which then acts as if it was just another, ordinary Oracle table and you can write (PL/)SQL against it).

[Updated on: Mon, 07 November 2011 06:44]

Report message to a moderator

Re: sqlload - 2nd file with updating information [message #530265 is a reply to message #530255]

Mon, 07 November 2011 07:37

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And here's a link to external table.

Regards
Michel

Report message to a moderator

Re: sqlload - 2nd file with updating information [message #530279 is a reply to message #530265]

Mon, 07 November 2011 08:22

strait
Messages: 3
Registered: November 2011

Junior Member

I just found this description:

Loading into Non-Empty Database Tables

SQL*Loader does not update existing records, even if they have null columns. If the tables you are loading already contain data, you have three choices for how SQL*Loader should proceed:
INSERT - This is the default option. It requires the table to be empty before loading. SQL*Loader terminates with an error if the table contains rows.

APPEND - If data already exists in the table, SQL*Loader appends the new rows to it; if data doesn't already exist, the new rows are simply loaded.

REPLACE - All rows in the table are deleted and the new data is loaded. This option requires DELETE privileges on the table.

When I do a replace I loose all data, also the ones from yesterday, the day before yesterday and so on... I just want to replace today mornings data. This is not possible, is it?

So I'd like to do your "otherwise"-part... how can I find out, which entries I have to delete?

[Updated on: Mon, 07 November 2011 08:27]

Report message to a moderator

Re: sqlload - 2nd file with updating information [message #530288 is a reply to message #530279]

Mon, 07 November 2011 09:20

Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA

Senior Member

You can use SQL*Loader whether your data files are on your server or your client to load into a staging table. You can only use an external table if your data files are on your server. The staging table or external table would not have any constraints. You can then merge the data from that staging table or external table into your target table. Please see the example below that uses the SQL*Loader option. I have used two different names for the data files and specified them in the command line, but you could use the same one and just overwrite if you like and specify it in the control file.

-- example1.csv:

03.11.2011;BFS;03:00:00;00:29:25;0,00572090;0,22408220;56,71504000
03.11.2011;BFS;03:30:28;00:30:29;0,00676938;0,29812120;55,06820000
03.11.2011;BFS;04:04:56;00:28:54;0,00315933;0,15332050;61,68312000
03.11.2011;BFS;04:33:50;00:59:33;0,00093421;0,04295300;59,57108000

-- example2.csv:

03.11.2011;BFS;03:00:00;00:27:25;0,00572090;0,22408220;56,71504000
03.11.2011;BFS;03:30:28;00:31:29;0,00676938;0,29812120;55,06820000
03.11.2011;BFS;04:04:56;00:29:54;0,00315933;0,15332050;61,68312000
03.11.2011;BFS;04:33:50;00:60:33;0,00093421;0,04295300;59,57108000

-- test.ctl:

load data
badfile 'example.bad'
replace
into table staging
fields terminated by ';' optionally enclosed by '"'
(
DATUM "TO_DATE(:DATUM,'DD.MM.YYYY')", PROG,
STARTZEIT "TO_TIMESTAMP(:STARTZEIT,'HH24:MI:SS')",
DAUER "TO_TIMESTAMP(:DAUER,'HH24:MI:SS')",
ZGESMIO, ZGESMA, ZGESSA
)

-- staging table:

SCOTT@orcl_11gR2> create table staging
  2    (datum	   date,
  3  	prog	   varchar2(4),
  4  	startzeit  timestamp,
  5  	dauer	   timestamp,
  6  	zgesmio    number,
  7  	zgesma	   number,
  8  	zgessa	   number)
  9  /

Table created.

-- target table:

SCOTT@orcl_11gR2> create table mf_sendung
  2    (datum	   date,
  3  	prog	   varchar2(4),
  4  	startzeit  timestamp,
  5  	dauer	   timestamp,
  6  	zgesmio    number,
  7  	zgesma	   number,
  8  	zgessa	   number,
  9  	constraint mf_sendung_pk
 10  		   primary key (datum, prog, startzeit))
 11  /

Table created.

-- first load into staging table, then merge into target table:

SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl data=example1.csv log=test1.log

SCOTT@orcl_11gR2> select * from staging
  2  /

DATUM    PROG
-------- ----
STARTZEIT
---------------------------------------------------------------------------
DAUER
---------------------------------------------------------------------------
   ZGESMIO     ZGESMA     ZGESSA
---------- ---------- ----------
03.11.11 BFS
01.11.11 03:00:00,000000
01.11.11 00:29:25,000000
  ,0057209   ,2240822   56,71504

03.11.11 BFS
01.11.11 03:30:28,000000
01.11.11 00:30:29,000000
 ,00676938   ,2981212    55,0682

03.11.11 BFS
01.11.11 04:04:56,000000
01.11.11 00:28:54,000000
 ,00315933   ,1533205   61,68312

03.11.11 BFS
01.11.11 04:33:50,000000
01.11.11 00:59:33,000000
 ,00093421    ,042953   59,57108


4 rows selected.

SCOTT@orcl_11gR2> merge into mf_sendung t
  2  using staging s
  3  on    (t.datum	= s.datum and
  4  	    t.prog	= s.prog  and
  5  	    t.startzeit = s.startzeit)
  6  when matched then
  7  	   update set t.dauer	= s.dauer,
  8  		      t.zgesmio = s.zgesmio,
  9  		      t.zgesma	= s.zgesma,
 10  		      t.zgessa	= s.zgessa
 11  when not matched then
 12  	   insert (t.datum, t.prog, t.startzeit, t.dauer, t.zgesmio, t.zgesma, t.zgessa)
 13  	   values (s.datum, s.prog, s.startzeit, s.dauer, s.zgesmio, s.zgesma, s.zgessa)
 14  /

4 rows merged.

SCOTT@orcl_11gR2> select * from mf_sendung
  2  /

DATUM    PROG
-------- ----
STARTZEIT
---------------------------------------------------------------------------
DAUER
---------------------------------------------------------------------------
   ZGESMIO     ZGESMA     ZGESSA
---------- ---------- ----------
03.11.11 BFS
01.11.11 04:04:56,000000
01.11.11 00:28:54,000000
 ,00315933   ,1533205   61,68312

03.11.11 BFS
01.11.11 04:33:50,000000
01.11.11 00:59:33,000000
 ,00093421    ,042953   59,57108

03.11.11 BFS
01.11.11 03:30:28,000000
01.11.11 00:30:29,000000
 ,00676938   ,2981212    55,0682

03.11.11 BFS
01.11.11 03:00:00,000000
01.11.11 00:29:25,000000
  ,0057209   ,2240822   56,71504


4 rows selected.

-- second load into staging table, then merge into target table:

SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl data=example2.csv log=test2.log

SCOTT@orcl_11gR2> select * from staging
  2  /

DATUM    PROG
-------- ----
STARTZEIT
---------------------------------------------------------------------------
DAUER
---------------------------------------------------------------------------
   ZGESMIO     ZGESMA     ZGESSA
---------- ---------- ----------
03.11.11 BFS
01.11.11 03:00:00,000000
01.11.11 00:27:25,000000
  ,0057209   ,2240822   56,71504

03.11.11 BFS
01.11.11 03:30:28,000000
01.11.11 00:31:29,000000
 ,00676938   ,2981212    55,0682

03.11.11 BFS
01.11.11 04:04:56,000000
01.11.11 00:29:54,000000
 ,00315933   ,1533205   61,68312


3 rows selected.

SCOTT@orcl_11gR2> merge into mf_sendung t
  2  using staging s
  3  on    (t.datum	= s.datum and
  4  	    t.prog	= s.prog  and
  5  	    t.startzeit = s.startzeit)
  6  when matched then
  7  	   update set t.dauer	= s.dauer,
  8  		      t.zgesmio = s.zgesmio,
  9  		      t.zgesma	= s.zgesma,
 10  		      t.zgessa	= s.zgessa
 11  when not matched then
 12  	   insert (t.datum, t.prog, t.startzeit, t.dauer, t.zgesmio, t.zgesma, t.zgessa)
 13  	   values (s.datum, s.prog, s.startzeit, s.dauer, s.zgesmio, s.zgesma, s.zgessa)
 14  /

3 rows merged.

SCOTT@orcl_11gR2> select * from mf_sendung
  2  /

DATUM    PROG
-------- ----
STARTZEIT
---------------------------------------------------------------------------
DAUER
---------------------------------------------------------------------------
   ZGESMIO     ZGESMA     ZGESSA
---------- ---------- ----------
03.11.11 BFS
01.11.11 04:04:56,000000
01.11.11 00:29:54,000000
 ,00315933   ,1533205   61,68312

03.11.11 BFS
01.11.11 04:33:50,000000
01.11.11 00:59:33,000000
 ,00093421    ,042953   59,57108

03.11.11 BFS
01.11.11 03:30:28,000000
01.11.11 00:31:29,000000
 ,00676938   ,2981212    55,0682

03.11.11 BFS
01.11.11 03:00:00,000000
01.11.11 00:27:25,000000
  ,0057209   ,2240822   56,71504


4 rows selected.

SCOTT@orcl_11gR2>

Report message to a moderator

Re: sqlload - 2nd file with updating information [message #530300 is a reply to message #530288]

Mon, 07 November 2011 09:57

Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA

Senior Member

Here is the same thing using an external table instead of SQL*Loader. I prefer the SQL*Loader method above. In the following, I had to remove an invalid row from your second data file or it would cause the second merge to fail. The invalid row had 60 in the minutes of the dauer (duration) column of timestamp datatype. The minutes must be 0 to 59. Once again, I used two different names for the data files, so I changed the location. Alternatively, you could just use the same name and overwrite.

-- example1.csv:

03.11.2011;BFS;03:00:00;00:29:25;0,00572090;0,22408220;56,71504000
03.11.2011;BFS;03:30:28;00:30:29;0,00676938;0,29812120;55,06820000
03.11.2011;BFS;04:04:56;00:28:54;0,00315933;0,15332050;61,68312000
03.11.2011;BFS;04:33:50;00:59:33;0,00093421;0,04295300;59,57108000

-- example2.csv:

03.11.2011;BFS;03:00:00;00:27:25;0,00572090;0,22408220;56,71504000
03.11.2011;BFS;03:30:28;00:31:29;0,00676938;0,29812120;55,06820000
03.11.2011;BFS;04:04:56;00:29:54;0,00315933;0,15332050;61,68312000

-- oracle directory object and external staging table accessing example1.csv:
-- (you may ned to grant read, write on the oracle directory object)

SCOTT@orcl_11gR2> create or replace directory my_dir as 'c:\my_oracle_files'
  2  /

Directory created.

SCOTT@orcl_11gR2> create table staging
  2    (datum	   varchar2(10),
  3  	prog	   varchar2( 4),
  4  	startzeit  varchar2( 8),
  5  	dauer	   varchar2( 8),
  6  	zgesmio    varchar2(11),
  7  	zgesma	   varchar2(11),
  8  	zgessa	   varchar2(11))
  9  ORGANIZATION external
 10  (
 11    TYPE oracle_loader
 12    DEFAULT DIRECTORY my_dir
 13    ACCESS PARAMETERS
 14    (
 15  	 RECORDS DELIMITED BY NEWLINE
 16  	 BADFILE 'MY_DIR':'example.bad'
 17  	 LOGFILE 'test1.log'
 18  	 FIELDS TERMINATED BY ";" OPTIONALLY ENCLOSED BY '"' LDRTRIM
 19  	 REJECT ROWS WITH ALL NULL FIELDS
 20    )
 21    location ('example1.csv')
 22  )REJECT LIMIT UNLIMITED
 23  /

Table created.

SCOTT@orcl_11gR2> select * from staging
  2  /

DATUM      PROG STARTZEI DAUER    ZGESMIO     ZGESMA      ZGESSA
---------- ---- -------- -------- ----------- ----------- -----------
03.11.2011 BFS  03:00:00 00:29:25 0,00572090  0,22408220  56,71504000
03.11.2011 BFS  03:30:28 00:30:29 0,00676938  0,29812120  55,06820000
03.11.2011 BFS  04:04:56 00:28:54 0,00315933  0,15332050  61,68312000
03.11.2011 BFS  04:33:50 00:59:33 0,00093421  0,04295300  59,57108000

4 rows selected.

-- target table:

SCOTT@orcl_11gR2> create table mf_sendung
  2    (datum	   date,
  3  	prog	   varchar2(4),
  4  	startzeit  timestamp,
  5  	dauer	   timestamp,
  6  	zgesmio    number,
  7  	zgesma	   number,
  8  	zgessa	   number,
  9  	constraint mf_sendung_pk
 10  		   primary key (datum, prog, startzeit))
 11  /

Table created.

-- first merge into target table:

SCOTT@orcl_11gR2> merge into mf_sendung t
  2  using staging s
  3  on    (to_date (s.datum, 'dd.mm.yyyy')	     = t.datum and
  4  	    s.prog				     = t.prog  and
  5  	    to_timestamp (s.startzeit, 'hh24:mi:ss') = t.startzeit)
  6  when matched then
  7  	   update set t.dauer	= to_timestamp (s.dauer, 'hh24:mi:ss'),
  8  		      t.zgesmio = to_number (s.zgesmio),
  9  		      t.zgesma	= to_number (s.zgesma),
 10  		      t.zgessa	= to_number (s.zgessa)
 11  when not matched then
 12  	   insert (t.datum, t.prog, t.startzeit, t.dauer, t.zgesmio, t.zgesma, t.zgessa)
 13  	   values (to_date (s.datum, 'dd.mm.yyyy'), s.prog,
 14  		   to_timestamp (s.startzeit, 'hh24:mi:ss'),
 15  		   to_timestamp (s.dauer, 'hh24:mi:ss'),
 16  		   to_number (s.zgesmio), to_number (s.zgesma), to_number (s.zgessa))
 17  /

4 rows merged.

SCOTT@orcl_11gR2> select * from mf_sendung
  2  /

DATUM     PROG
--------- ----
STARTZEIT
---------------------------------------------------------------------------
DAUER
---------------------------------------------------------------------------
   ZGESMIO     ZGESMA     ZGESSA
---------- ---------- ----------
03-NOV-11 BFS
01-NOV-11 03.00.00,000000 AM
01-NOV-11 12.29.25,000000 AM
  ,0057209   ,2240822   56,71504

03-NOV-11 BFS
01-NOV-11 03.30.28,000000 AM
01-NOV-11 12.30.29,000000 AM
 ,00676938   ,2981212    55,0682

03-NOV-11 BFS
01-NOV-11 04.04.56,000000 AM
01-NOV-11 12.28.54,000000 AM
 ,00315933   ,1533205   61,68312

03-NOV-11 BFS
01-NOV-11 04.33.50,000000 AM
01-NOV-11 12.59.33,000000 AM
 ,00093421    ,042953   59,57108


4 rows selected.

-- alter location of external staging table to access example2.csv:

SCOTT@orcl_11gR2> alter table staging location ('example2.csv')
  2  /

Table altered.

SCOTT@orcl_11gR2> select * from staging
  2  /

DATUM      PROG STARTZEI DAUER    ZGESMIO     ZGESMA      ZGESSA
---------- ---- -------- -------- ----------- ----------- -----------
03.11.2011 BFS  03:00:00 00:27:25 0,00572090  0,22408220  56,71504000
03.11.2011 BFS  03:30:28 00:31:29 0,00676938  0,29812120  55,06820000
03.11.2011 BFS  04:04:56 00:29:54 0,00315933  0,15332050  61,68312000

3 rows selected.

-- second merge into target table:

SCOTT@orcl_11gR2> merge into mf_sendung t
  2  using staging s
  3  on    (to_date (s.datum, 'dd.mm.yyyy')	     = t.datum and
  4  	    s.prog				     = t.prog  and
  5  	    to_timestamp (s.startzeit, 'hh24:mi:ss') = t.startzeit)
  6  when matched then
  7  	   update set t.dauer	= to_timestamp (s.dauer, 'hh24:mi:ss'),
  8  		      t.zgesmio = to_number (s.zgesmio),
  9  		      t.zgesma	= to_number (s.zgesma),
 10  		      t.zgessa	= to_number (s.zgessa)
 11  when not matched then
 12  	   insert (t.datum, t.prog, t.startzeit, t.dauer, t.zgesmio, t.zgesma, t.zgessa)
 13  	   values (to_date (s.datum, 'dd.mm.yyyy'), s.prog,
 14  		   to_timestamp (s.startzeit, 'hh24:mi:ss'),
 15  		   to_timestamp (s.dauer, 'hh24:mi:ss'),
 16  		   to_number (s.zgesmio), to_number (s.zgesma), to_number (s.zgessa))
 17  /

3 rows merged.

SCOTT@orcl_11gR2> select * from mf_sendung
  2  /

DATUM     PROG
--------- ----
STARTZEIT
---------------------------------------------------------------------------
DAUER
---------------------------------------------------------------------------
   ZGESMIO     ZGESMA     ZGESSA
---------- ---------- ----------
03-NOV-11 BFS
01-NOV-11 03.00.00,000000 AM
01-NOV-11 12.27.25,000000 AM
  ,0057209   ,2240822   56,71504

03-NOV-11 BFS
01-NOV-11 03.30.28,000000 AM
01-NOV-11 12.31.29,000000 AM
 ,00676938   ,2981212    55,0682

03-NOV-11 BFS
01-NOV-11 04.04.56,000000 AM
01-NOV-11 12.29.54,000000 AM
 ,00315933   ,1533205   61,68312

03-NOV-11 BFS
01-NOV-11 04.33.50,000000 AM
01-NOV-11 12.59.33,000000 AM
 ,00093421    ,042953   59,57108


4 rows selected.

SCOTT@orcl_11gR2>

Report message to a moderator

Re: sqlload - 2nd file with updating information [message #530437 is a reply to message #530300]

Tue, 08 November 2011 03:42

strait
Messages: 3
Registered: November 2011

Junior Member

Thank you so much!! Smile

Report message to a moderator

Previous Topic:	Virtualizing a Server running with ASM Storage.
Next Topic:	Error while import ( impdp )

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 12 18:07:13 CST 2025