Insert and update rows from a file in oracle

Question

I have a file in linux, the file is something like: (I have millions of rows)

 date              number      name           id          state
20131110            1089      name1          123           start
20131110            1080      name2          122           start
20131110            1082      name3          121           start
20131114            1089      name1          120           end
20131115            1082      name3          119           end

And i have a table in Oracle with the following fileds:

init_table

start_date
end_date
number
name
id

The problem is that i read that i can insert data with a sqlloader, (I have millions of rows, then create a temporal table to insert and later with a trigger update the other table is not well) the problem is that I have an user with start date X, for example the number 1089 has the start date: 20131110, and the end_date of this user is: 20131114, then i need insert first the start_date in my table, later when i found the end_date, update my table of the number that i am inserting, that in my example is 1089 with the end date that is: 20131114.

How can do it with a ctl, or with other thing.

Who can help me. Thanks

score 2 · Answer 1 · answered Nov 15 '13 at 15:02

What version of Oracle?

I would use an external table. Define an external table that exactly matches your flat file. Then, you should be able to solve this with two passes, one insert, one update.

Something like this should do it:

insert into init_table select to_date(date,'YYYYMMDD'),null,number,name,id from external_table where state='start';

update init_table set end_date=(select date from external_table where state='end' and init_table.number=external_table.number);

Note that you can't actually have columns named 'date' or 'number', so, the sql above isn't actually going to work as written. You'll have to change those column names.

Hope that helps...

The version is Oracle Database 10g Enterprise Edition 10.2.0.4.0 64bi. But i do not understand how you insert the bash file? — user2768380, Nov 19 '13 at 20:51

score 1 · Answer 2 · edited Nov 15 '13 at 16:50

1

$ cat sqlldrNew.ctl

load data
 infile '/home/Sameer/employee.txt'
 into table employee
 fields terminated by X'9'
 ( date, -->select number from employee where name=[Name from the file record], name, id, state )

$ sqlldr scott/tiger control=/home/Sameer/sqlldrNew.ctl

I think this should work.

edited Nov 15 '13 at 16:50

answered Nov 15 '13 at 15:42

Sameer Sawla

729
6
20

score 1 · Answer 3 · answered Nov 15 '13 at 16:48

If you use an external table approach then you can join the data in the external table to itself to produce a single record that can then be inserted. Although the join is expensive, overall it ought to be an efficient process as long as the hash join I'd expect to be used stays in memory.

So something like ...

insert into init_table (
  start_date,
  end_date,
  number,
  name,
  id)
select
  s.date,
  e.date,
  s.number,
  s.name,
  s.id
from external_table s
join external_table e on s.number = e.number
where
  s.state = 'start' and
  e.state = 'end'

That assumes that there will always be an end date for every start date, and that the number does not already exist in the table -- if either of those conditions is not true then an outer join would be required in the former case, and a merge required in the latter.

The case is that the file not necessary has the end_date, with the start_date, u have this file today, but in 1 or 2 months i can have the file with the end_date of the start_date of 2 months ago — user2768380, Nov 19 '13 at 20:54
In that case you'd full outer join the start and end records and perform a merge against the target table. — David Aldridge, Nov 19 '13 at 23:31

Insert and update rows from a file in oracle

3 Answers3