Home » RDBMS Server » Server Utilities » load data from another table/flat file (11.2.0.4,Windows 2008 R2)
load data from another table/flat file [message #636285] Mon, 20 April 2015 16:45 Go to next message
sant_new1
Messages: 46
Registered: June 2014
Member
Hi friends,

I'm looking for some help loading data onto a table, below is the table definition.
create table prp (
  prp_name varchar2(1) not null,
  i_name varchar2(1) not null,
  prp_id number(22) not null,
  prp_date date,
  br_prp varchar2(255),
  prp_partner number(22),
  prp_seq_no number(22),
  product_type varchar2(10) not null,
  prp_ref_no varchar2(255) not null,
  m_user varchar2(30) not null,
  prp_comp varchar2(10),
  prp_code varchar2(10),
  first_name varchar2(40),
  prp_dept varchar2(60),
  prp_street varchar2(10)
);

We will be getting data file from customer for the fields below, I will be loading this data onto the below tmp_prp table.
create table tmp_prp (
  product varchar2(10) not null,
  product_type varchar2(10) not null,
  tseq_no number(22) not null,
  ref_no varchar2(255) not null,
  fname varchar2(40),
  lname varchar2(40),
  street varchar2(40)
);
 
Data from customer in xls (I've converted to csv seperated by |). This data is loaded onto tmp_prp table.
 
'Med'|'STR'|45|'HR4'|'Francis'|''|'Main st'
'Medpr'|'STR'|18|'HR9'|'Andrea'|'Nelson'|'Swift Way'
'PharK'|'RKN'|22|'IR2'|'Mary'|''|'Swift Way'

For every above record in tmp_prp table(data from csv), we will need to load data onto table prp. Some columns in PRP table to be set with default values, some columns with the data from tmp_prp table. Below are the mapping details to load prp table..

prp.prp_name - 'P'
prp.i_name - 'I'
prp.prp_id - 1
prp.prp_date - sysdate
prp.br_prp - NULL
prp.prp_partner - Sequence starting from 1
prp.prp_seq_no - tmp_prp.tseq_no
prp.product_type - tmp_prp.product_type
prp.prp_ref_no - tmp_prp.ref_no
prp.c_user - 'TST'
prp.prp_comp - '%'
prp.prp_code - tmp_prp.product if NOT NULL else default to 'PRIN'
prp.first_name - tmp_prp.fname if tmp_prp.product_type='STR' else NULL
prp.prp_dept - tmp_prp.fname+''+tmp_prp.lname if tmp_prp.product_type='STR' and tmp_prp.lname is not NULL
prp.prp_street - tmp_prp.street(1,40)

I'm not sure if I can use sql loader to load PRP table since I will need to pull data from tmp_prp based on some mapping conditions above..
Please note that I've provided sample data here for convenience.. We will be getting 1000s of records to be loaded onto PRP table.. Please help...
Thank you so much

[Updated on: Mon, 20 April 2015 17:00]

Report message to a moderator

Re: load data from another table/flat file [message #636286 is a reply to message #636285] Mon, 20 April 2015 18:02 Go to previous messageGo to next message
BlackSwan
Messages: 26545
Registered: January 2009
Location: SoCal
Senior Member
OK, you have an implementation challenge.
I assume this is what you employer is paying you to do.
So what exactly do you desire & expect us to do now?

Is there a one to one relationship between records in TMP_PRP table & records in PRP table?
Re: load data from another table/flat file [message #636289 is a reply to message #636286] Mon, 20 April 2015 19:27 Go to previous messageGo to next message
sant_new1
Messages: 46
Registered: June 2014
Member
Thanks for your reply. I'm just looking out for any help/approach from experts..

There is no such one to one relationship(parent/child) between the tables, but for every record in tmp_prp table there should be an insert into PRP table. In the data join(mapping), prp.prp_seq_no = tmp_prp.tseq_no and prp.product_type = tmp_prp.product_type. We will need to load the data onto PRP table(based on the data mapping above) either using the flat file data or from tmp_prp(created with data from flat file).. Please give me your suggestions.. Greatly Appreciate all your help.

Thank you
Re: load data from another table/flat file [message #636291 is a reply to message #636285] Mon, 20 April 2015 20:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8916
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl> HOST TYPE test.dat
'Med'|'STR'|45|'HR4'|'Francis'|''|'Main st'
'Medpr'|'STR'|18|'HR9'|'Andrea'|'Nelson'|'Swift Way'
'PharK'|'RKN'|22|'IR2'|'Mary'|''|'Swift Way'

SCOTT@orcl> HOST TYPE test.ctl
LOAD DATA
INTO TABLE tmp_prp
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY "'" TRAILING NULLCOLS
( product       POSITION(1)
, product_type
, tseq_no
, ref_no
, fname
, lname
, street )
INTO TABLE prp
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY "'" TRAILING NULLCOLS
( prp_code      POSITION(1)  "NVL (:prp_code, 'PRIN')"
, product_type
, prp_seq_no
, prp_ref_no
, first_name    "CASE WHEN :product_type = 'STR' THEN :first_name ELSE NULL END"
, lname         BOUNDFILLER
, prp_street
, prp_name      CONSTANT 'P'
, i_name        CONSTANT 'I'
, prp_id        CONSTANT  1
, prp_date      SYSDATE
, prp_partner   SEQUENCE
, m_user        CONSTANT 'TST'
, prp_comp      CONSTANT '%'
, prp_dept      "CASE WHEN :product_type='STR' AND :lname IS NOT NULL THEN :first_name||:lname END")

SCOTT@orcl> create table prp (
  2    prp_name varchar2(1) not null,
  3    i_name varchar2(1) not null,
  4    prp_id number(22) not null,
  5    prp_date date,
  6    br_prp varchar2(255),
  7    prp_partner number(22),
  8    prp_seq_no number(22),
  9    product_type varchar2(10) not null,
 10    prp_ref_no varchar2(255) not null,
 11    m_user varchar2(30) not null,
 12    prp_comp varchar2(10),
 13    prp_code varchar2(10),
 14    first_name varchar2(40),
 15    prp_dept varchar2(60),
 16    prp_street varchar2(10)
 17  );

Table created.

SCOTT@orcl> create table tmp_prp (
  2    product varchar2(10) not null,
  3    product_type varchar2(10) not null,
  4    tseq_no number(22) not null,
  5    ref_no varchar2(255) not null,
  6    fname varchar2(40),
  7    lname varchar2(40),
  8    street varchar2(40)
  9  );

Table created.

SCOTT@orcl> HOST SQLLDR scott/tiger CONTROL=test.ctl DATA=test.dat LOG=test.log

SQL*Loader: Release 11.2.0.1.0 - Production on Mon Apr 20 17:58:21 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 3

SCOTT@orcl> COLUMN ref_no FORMAT A6
SCOTT@orcl> COLUMN fname  FORMAT A7
SCOTT@orcl> COLUMN lname  FORMAT A6
SCOTT@orcl> COLUMN street FORMAT A9
SCOTT@orcl> SELECT * FROM tmp_prp
  2  /

PRODUCT    PRODUCT_TY    TSEQ_NO REF_NO FNAME   LNAME  STREET
---------- ---------- ---------- ------ ------- ------ ---------
Med        STR                45 HR4    Francis        Main st
Medpr      STR                18 HR9    Andrea  Nelson Swift Way
PharK      RKN                22 IR2    Mary           Swift Way

3 rows selected.

SCOTT@orcl> COLUMN br_prp         FORMAT A6
SCOTT@orcl> COLUMN prp_ref_no FORMAT A10
SCOTT@orcl> COLUMN m_user         FORMAT A6
SCOTT@orcl> COLUMN first_name FORMAT A10
SCOTT@orcl> COLUMN prp_dept   FORMAT A12
SCOTT@orcl> SELECT * FROM prp
  2  /

P I     PRP_ID PRP_DATE        BR_PRP PRP_PARTNER PRP_SEQ_NO PRODUCT_TY PRP_REF_NO M_USER PRP_COMP   PRP_CODE   FIRST_NAME PR
P_DEPT  PRP_STREET
- - ---------- --------------- ------ ----------- ---------- ---------- ---------- ------ ---------- ---------- ---------- --
---------- ----------
P I          1 Mon 20-Apr-2015                  1         45 STR        HR4        TST    %          Med        Francis
        Main st
P I          1 Mon 20-Apr-2015                  2         18 STR        HR9        TST    %          Medpr      Andrea     An
dreaNelson Swift Way
P I          1 Mon 20-Apr-2015                  3         22 RKN        IR2        TST    %          PharK
        Swift Way

3 rows selected.

Re: load data from another table/flat file [message #636294 is a reply to message #636291] Mon, 20 April 2015 21:11 Go to previous messageGo to next message
sant_new1
Messages: 46
Registered: June 2014
Member
Thank you so much.. This works perfect for this data load and I can use this approach for other data loads on different tables. Thanks again for your help
Re: load data from another table/flat file [message #636707 is a reply to message #636294] Thu, 30 April 2015 09:51 Go to previous messageGo to next message
sant_new1
Messages: 46
Registered: June 2014
Member
I've inserted tables with multiple columns(different criteria) successfully.. But, I'm needing some help with inserting a varchar field+sequence
for the field 'm_user' in this same table...
In the above ctl file, instead of the value constant 'TST' for 'm_user' it should be this criteria - when :product_type='STR' then 'STR'||sequence, when :product_type='RKN' then 'RKN'||sequence
(for example, for product_type 'STR', m_user should be STR1,STR2,.. and for product_Type 'RKN', m_user should be RKN1,RKN2..)
I tried as below, but it didn't work...

, m_user "CASE WHEN :product_type='STR' THEN 'STR'||SEQUENCE WHEN :product_type='RKN' THEN 'RKN'||SEQUENCE END"

Please help.. Thank you
icon12.gif  Re: load data from another table/flat file [message #636714 is a reply to message #636285] Thu, 30 April 2015 12:11 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
If you seek a more dynamic way to load data, try an ETL utility like CloverETL or Pentaho, both have "community" editions (free).
These tools can load from almost any source (excel, xml, flat file, any rdbms, etc...) into almost any target similar to the source, they can perform all kind of transformations, data filtering, db lookups, joins, etc...
Its worth a try.
Good luck!
PS: You avoid all the coding.

[Updated on: Thu, 30 April 2015 12:14]

Report message to a moderator

Re: load data from another table/flat file [message #636715 is a reply to message #636707] Thu, 30 April 2015 12:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8916
Registered: November 2002
Location: California, USA
Senior Member
, prp_partner SEQUENCE
, m_user "CASE WHEN :product_type='STR' THEN 'STR'||:prp_partner
WHEN :product_type='RKN' THEN 'RKN'||:prp_partner END"
Re: load data from another table/flat file [message #636717 is a reply to message #636715] Thu, 30 April 2015 12:50 Go to previous messageGo to next message
sant_new1
Messages: 46
Registered: June 2014
Member
Thanks Barbara. But, prp_partner sequence cannot be used here because we need m_user to be in a sequence order for 'STR' and 'RKN' independently.. FOr example,
in the below 6 records,

product_type='STR' and prp_partner=1, m_user should be 'STR1'
product_type='RKN' and prp_partner=2, m_user should be 'RKN1'
product_type='RKN' and prp_partner=3, m_user should be 'RKN2'
product_type='STR' and prp_partner=4, m_user should be 'STR2'
product_type='RKN' and prp_partner=5, m_user should be 'RKN3'
product_type='STR' and prp_partner=6, m_user should be 'STR3'

Thank you so much for your help...
Re: load data from another table/flat file [message #636726 is a reply to message #636717] Thu, 30 April 2015 19:26 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8916
Registered: November 2002
Location: California, USA
Senior Member
-- run once in SQL:
CREATE SEQUENCE str_seq NOCACHE ORDER
/
CREATE SEQUENCE rkn_seq NOCACHE ORDER
/
CREATE OR REPLACE FUNCTION get_seq
  (p_product_type IN VARCHAR2)
  RETURN             VARCHAR2
AS
BEGIN
  IF p_product_type = 'STR'
    THEN RETURN 'STR' || str_seq.NEXTVAL;
  ELSIF p_product_type = 'RKN'
    THEN RETURN 'RKN' || rkn_seq.NEXTVAL;
  ELSE
    RETURN p_product_type;
  END IF;
END get_seq;
/


-- in SQL*Loader control file:
m_user  "get_seq (:product_type)"

[Updated on: Thu, 30 April 2015 19:29]

Report message to a moderator

Re: load data from another table/flat file [message #636732 is a reply to message #636726] Thu, 30 April 2015 22:33 Go to previous message
sant_new1
Messages: 46
Registered: June 2014
Member
Thank you so much again.. Works perfect!
Previous Topic: how to import dump of one databse to another database
Next Topic: export from 11g and import to 10g
Goto Forum:
  


Current Time: Wed Jun 26 03:12:04 CDT 2019