Home » SQL & PL/SQL » SQL & PL/SQL » How can bulk insert into the parent , child tables (Oracle Linux 6.7, Oracle 12.1.0.2.0)
How can bulk insert into the parent , child tables [message #670473] |
Sun, 08 July 2018 01:27 |
|
fabi88
Messages: 112 Registered: November 2011
|
Senior Member |
|
|
There is is a flat file (CV file) which has Countries,Provinces and Cities information, like:
Germany, Berlin, Potsdam
Germany, Hamburg, Altona
Germany, Hamburg, Reinbek
there are three tables in the database for keeping countries, provinces and cities, like:
# Country Table
Country_ID , Country_Name
# Province Table
Province_ID ,Country_ID (F.K to Country Table), Province_Name
# City Table
City_ID,Province_ID(F.K to Province Table) City_Name
how can bulk insert into the tables using something like external table ,... or any other methods which can be used for bulk insert?
I mean that how can bulk insert into the parent , child tables?
Thanks for your favor in advance.
|
|
|
|
|
|
|
|
|
Re: How can bulk insert into the parent , child tables [message #670484 is a reply to message #670475] |
Sun, 08 July 2018 09:42 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
fabi88 wrote on Sun, 08 July 2018 02:56Thank you so much for your useful reply.
Do you mean something like this?
But how can get the inserted country_id, province_id and city_id?(Shown with question mark)
1. Not like this. Using unconditional INSERT ALL will result in COUNTRY and PROVINCE inserted as many times as there are rows in EXTERNAL_TABLE_INFO table.
2. IDENTITY column is implemented by setting column default value to generated sequence. You could get sequence name from USER_TAB_COLUMNS.DATA_DEFAULT.
But unfortunately identity column value is calculated before evaluating condition. You could tun off IDENTITY column, do INSERT ALL and turn it back on (keep in mind changing IDENTITY options affects all sessions):
SQL> CREATE TABLE COUNTRIES(
2 COUNTRY_ID NUMBER GENERATED ALWAYS AS IDENTITY,
3 COUNTRY_NAME VARCHAR2(30)
4 )
5 /
Table created.
SQL> CREATE TABLE PROVINCES(
2 COUNTRY_ID NUMBER,
3 PROVINCE_ID NUMBER GENERATED ALWAYS AS IDENTITY,
4 PROVINCE_NAME VARCHAR2(30)
5 )
6 /
Table created.
SQL> CREATE TABLE CITIES(
2 PROVINCE_ID NUMBER,
3 CITY_NAME VARCHAR2(30)
4 )
5 /
Table created.
SQL> CREATE TABLE EXTERNAL_TABLE_INFO(
2 COUNTRY_NAME VARCHAR2(30),
3 PROVINCE_NAME VARCHAR2(30),
4 CITY_NAME VARCHAR2(30)
5 )
6 /
Table created.
SQL> INSERT
2 INTO EXTERNAL_TABLE_INFO
3 VALUES(
4 'CANADA',
5 'ONTARIO',
6 'TORONTO'
7 )
8 /
1 row created.
SQL> INSERT
2 INTO EXTERNAL_TABLE_INFO
3 VALUES(
4 'BELGIUM',
5 'ANTVERP',
6 'ANTVERP'
7 )
8 /
1 row created.
SQL> INSERT
2 INTO EXTERNAL_TABLE_INFO
3 VALUES(
4 'CANADA',
5 'QUEBEC',
6 'QUEBEC CITY'
7 )
8 /
1 row created.
SQL> INSERT
2 INTO EXTERNAL_TABLE_INFO
3 VALUES(
4 'BELGIUM',
5 'NAMUR',
6 'NAMUR'
7 )
8 /
1 row created.
SQL> INSERT
2 INTO EXTERNAL_TABLE_INFO
3 VALUES(
4 'CANADA',
5 'ALBERTA',
6 'EDMONTON'
7 )
8 /
1 row created.
SQL> INSERT
2 INTO EXTERNAL_TABLE_INFO
3 VALUES(
4 'CANADA',
5 'QUEBEC',
6 'MONTREAL'
7 )
8 /
1 row created.
SQL> -- Temporarily change IDENTITY generation from ALWAYS to DEFAULT
SQL> -- Keep in mind this affects ALL sessions.
SQL> ALTER TABLE COUNTRIES
2 MODIFY COUNTRY_ID GENERATED BY DEFAULT AS IDENTITY
3 /
Table altered.
SQL> ALTER TABLE PROVINCES
2 MODIFY PROVINCE_ID GENERATED BY DEFAULT AS IDENTITY
3 /
Table altered.
SQL> INSERT ALL
2 WHEN COUNTRY_RN = 1
3 THEN
4 INTO COUNTRIES
5 VALUES(
6 COUNTRY_ID,
7 COUNTRY_NAME
8 )
9 WHEN PROVINCE_RN = 1
10 THEN
11 INTO PROVINCES
12 VALUES(
13 COUNTRY_ID,
14 PROVINCE_ID,
15 PROVINCE_NAME
16 )
17 WHEN 1 = 1
18 THEN
19 INTO CITIES
20 VALUES(
21 PROVINCE_ID,
22 CITY_NAME
23 )
24 SELECT E.*,
25 ROW_NUMBER() OVER(PARTITION BY COUNTRY_NAME ORDER BY PROVINCE_NAME,CITY_NAME) COUNTRY_RN,
26 ROW_NUMBER() OVER(PARTITION BY COUNTRY_NAME,PROVINCE_NAME ORDER BY CITY_NAME) PROVINCE_RN,
27 DENSE_RANK() OVER(ORDER BY COUNTRY_NAME) COUNTRY_ID,
28 DENSE_RANK() OVER(ORDER BY COUNTRY_NAME,PROVINCE_NAME) PROVINCE_ID
29 FROM EXTERNAL_TABLE_INFO E
30 /
13 rows created.
SQL> SELECT *
2 FROM COUNTRIES
3 /
COUNTRY_ID COUNTRY_NAME
---------- ------------
1 BELGIUM
2 CANADA
SQL> SELECT *
2 FROM PROVINCES
3 /
COUNTRY_ID PROVINCE_ID PROVINCE_NAME
---------- ----------- -------------
1 1 ANTVERP
1 2 NAMUR
2 3 ALBERTA
2 4 ONTARIO
2 5 QUEBEC
SQL> SELECT *
2 FROM CITIES
3 /
PROVINCE_ID CITY_NAME
----------- -----------
1 ANTVERP
2 NAMUR
3 EDMONTON
4 TORONTO
5 MONTREAL
5 QUEBEC CITY
6 rows selected.
SQL> -- Change IDENTITY generation from DEFAULT back to ALWAYS.
SQL> ALTER TABLE COUNTRIES
2 MODIFY COUNTRY_ID GENERATED ALWAYS AS IDENTITY
3 /
Table altered.
SQL> ALTER TABLE PROVINCES
2 MODIFY PROVINCE_ID GENERATED ALWAYS AS IDENTITY
3 /
Table altered.
SQL>
The above assumes tables are empty. Otherwise, get MAX for COUNTRY_ID, PROVINCE_ID and add to DENSE_RANK calculated value.
SY.
|
|
|
Re: How can bulk insert into the parent , child tables [message #670487 is a reply to message #670473] |
Sun, 08 July 2018 21:58 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
I would probably be inclined to use SQL*Loader, doing three separate loads with three separate control files, after adding some unique constraints, if you do not already have them. This adds the option of loading from the client instead of the server, handles duplicates by loading one of them instead of rejecting all of them, and eliminates the need to modify any identity columns.
-- If you have a data file like this:
SCOTT@orcl_12.1.0.2.0> host type c:\my_oracle_files\flat_file.csv
Germany, Berlin, Potsdam
Germany, Hamburg, Altona
Germany, Hamburg, Reinbek
CANADA, ONTARIO, TORONTO
BELGIUM, ANTVERP, ANTVERP
CANADA, QUEBEC, QUEBEC CITY
BELGIUM, NAMUR, NAMUR
CANADA, ALBERTA, EDMONTON
CANADA, QUEBEC, MONTREAL
-- and you have tables with identity columns and primary and foreign keys, like this:
SCOTT@orcl_12.1.0.2.0> create table country
2 (country_id number generated always as identity primary key,
3 country_name varchar2(25))
4 /
Table created.
SCOTT@orcl_12.1.0.2.0> create table province
2 (province_id number generated always as identity primary key,
3 country_id number references country (country_id),
4 province_name varchar2(25))
5 /
Table created.
SCOTT@orcl_12.1.0.2.0> create table city
2 (city_id number generated always as identity primary key,
3 province_id number references province (province_id),
4 city_name varchar2(25))
5 /
Table created.
-- and you create some unique constraints, if you don't already have them, like this:
SCOTT@orcl_12.1.0.2.0> alter table country add constraint country_uk unique (country_name)
2 /
Table altered.
SCOTT@orcl_12.1.0.2.0> alter table province add constraint province_uk unique (country_id, province_name)
2 /
Table altered.
SCOTT@orcl_12.1.0.2.0> alter table city add constraint city_uk unique (province_id, city_name)
2 /
Table altered.
-- and you create three control files like these:
SCOTT@orcl_12.1.0.2.0> host type c:\my_oracle_files\country.ctl
load data
into table country
fields terminated by ',' trailing nullcols
( country_name )
SCOTT@orcl_12.1.0.2.0> host type c:\my_oracle_files\province.ctl
load data
into table province when province_name != ' '
fields terminated by ',' trailing nullcols
( country_fill boundfiller position(1)
, province_name
, country_id expression "(select country_id from country where country_name = :country_fill)" )
SCOTT@orcl_12.1.0.2.0> host type c:\my_oracle_files\city.ctl
load data
into table city
fields terminated by ',' trailing nullcols
( country_fill boundfiller position(1)
, province_fill boundfiller
, city_name
, province_id expression
"(select province_id from province, country
where province_name = :province_fill and country_name = :country_fill
and province.country_id = country.country_id)" )
-- then you can load the data like this:
SCOTT@orcl_12.1.0.2.0> host sqlldr scott/tiger data=c:\my_oracle_files\flat_file.csv control=c:\my_oracle_files\country.ctl log=country.log
SQL*Loader: Release 12.1.0.2.0 - Production on Sun Jul 8 19:47:33 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 9
Table COUNTRY:
3 Rows successfully loaded.
Check the log file:
country.log
for more information about the load.
SCOTT@orcl_12.1.0.2.0> host sqlldr scott/tiger data=c:\my_oracle_files\flat_file.csv control=c:\my_oracle_files\province.ctl log=province.log
SQL*Loader: Release 12.1.0.2.0 - Production on Sun Jul 8 19:47:33 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 9
Table PROVINCE:
7 Rows successfully loaded.
Check the log file:
province.log
for more information about the load.
SCOTT@orcl_12.1.0.2.0> host sqlldr scott/tiger data=c:\my_oracle_files\flat_file.csv control=c:\my_oracle_files\city.ctl log=city.log
SQL*Loader: Release 12.1.0.2.0 - Production on Sun Jul 8 19:47:33 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 9
Table CITY:
9 Rows successfully loaded.
Check the log file:
city.log
for more information about the load.
-- with the following results:
SCOTT@orcl_12.1.0.2.0> select * from country
2 /
COUNTRY_ID COUNTRY_NAME
---------- -------------------------
1 Germany
18 CANADA
19 BELGIUM
3 rows selected.
SCOTT@orcl_12.1.0.2.0> select * from province
2 /
PROVINCE_ID COUNTRY_ID PROVINCE_NAME
----------- ---------- -------------------------
1 1 Berlin
2 1 Hamburg
10 18 ONTARIO
11 19 ANTVERP
12 18 QUEBEC
13 19 NAMUR
14 18 ALBERTA
7 rows selected.
SCOTT@orcl_12.1.0.2.0> select * from city
2 /
CITY_ID PROVINCE_ID CITY_NAME
---------- ----------- -------------------------
1 1 Potsdam
2 2 Altona
3 2 Reinbek
4 10 TORONTO
5 11 ANTVERP
6 12 QUEBEC CITY
7 13 NAMUR
8 14 EDMONTON
9 12 MONTREAL
9 rows selected.
SCOTT@orcl_12.1.0.2.0> select country_name, province_name, city_name
2 from country, province, city
3 where country.country_id = province.country_id
4 and province.province_id = city.province_id
5 order by 1, 2, 3
6 /
COUNTRY_NAME PROVINCE_NAME CITY_NAME
------------------------- ------------------------- -------------------------
BELGIUM ANTVERP ANTVERP
BELGIUM NAMUR NAMUR
CANADA ALBERTA EDMONTON
CANADA ONTARIO TORONTO
CANADA QUEBEC MONTREAL
CANADA QUEBEC QUEBEC CITY
Germany Berlin Potsdam
Germany Hamburg Altona
Germany Hamburg Reinbek
9 rows selected.
|
|
|
Re: How can bulk insert into the parent , child tables [message #670488 is a reply to message #670487] |
Sun, 08 July 2018 23:14 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Another method would be to either use SQL*Loader to load the data into a staging table or use an external table as a staging table, then do three merge statements, as shown below.
-- data file:
SCOTT@orcl_12.1.0.2.0> host type c:\my_oracle_files\flat_file.csv
Germany, Berlin, Potsdam
Germany, Hamburg, Altona
Germany, Hamburg, Reinbek
CANADA, ONTARIO, TORONTO
BELGIUM, ANTVERP, ANTVERP
CANADA, QUEBEC, QUEBEC CITY
BELGIUM, NAMUR, NAMUR
CANADA, ALBERTA, EDMONTON
CANADA, QUEBEC, MONTREAL
-- Oracle directory object and external staging table:
SCOTT@orcl_12.1.0.2.0> create or replace directory my_dir as 'c:\my_oracle_files'
2 /
Directory created.
SCOTT@orcl_12.1.0.2.0> create table staging
2 (country_name varchar2(25),
3 province_name varchar2(25),
4 city_name varchar2(25))
5 ORGANIZATION external
6 (TYPE oracle_loader
7 DEFAULT DIRECTORY my_dir
8 ACCESS PARAMETERS
9 (RECORDS DELIMITED BY NEWLINE
10 LOGFILE 'staging.log'
11 FIELDS TERMINATED BY "," LDRTRIM
12 MISSING FIELD VALUES ARE NULL
13 REJECT ROWS WITH ALL NULL FIELDS
14 (country_name, province_name, city_name))
15 location ('flat_file.csv'))
16 REJECT LIMIT UNLIMITED
17 /
Table created.
SCOTT@orcl_12.1.0.2.0> select * from staging
2 /
COUNTRY_NAME PROVINCE_NAME CITY_NAME
------------------------- ------------------------- -------------------------
Germany Berlin Potsdam
Germany Hamburg Altona
Germany Hamburg Reinbek
CANADA ONTARIO TORONTO
BELGIUM ANTVERP ANTVERP
CANADA QUEBEC QUEBEC CITY
BELGIUM NAMUR NAMUR
CANADA ALBERTA EDMONTON
CANADA QUEBEC MONTREAL
9 rows selected.
-- tables with identity columns, primary and foreign keys, and optional unique constraints (not required for this method):
SCOTT@orcl_12.1.0.2.0> create table country
2 (country_id number generated always as identity primary key,
3 country_name varchar2(25))
4 /
Table created.
SCOTT@orcl_12.1.0.2.0> create table province
2 (province_id number generated always as identity primary key,
3 country_id number references country (country_id),
4 province_name varchar2(25))
5 /
Table created.
SCOTT@orcl_12.1.0.2.0> create table city
2 (city_id number generated always as identity primary key,
3 province_id number references province (province_id),
4 city_name varchar2(25))
5 /
Table created.
SCOTT@orcl_12.1.0.2.0> alter table country add constraint country_uk unique (country_name)
2 /
Table altered.
SCOTT@orcl_12.1.0.2.0> alter table province add constraint province_uk unique (country_id, province_name)
2 /
Table altered.
SCOTT@orcl_12.1.0.2.0> alter table city add constraint city_uk unique (province_id, city_name)
2 /
Table altered.
-- merge statements:
SCOTT@orcl_12.1.0.2.0> merge into country c
2 using (select distinct country_name
3 from staging) s
4 on (c.country_name = s.country_name)
5 when not matched then insert (c.country_name)
6 values (s.country_name)
7 /
3 rows merged.
SCOTT@orcl_12.1.0.2.0> commit
2 /
Commit complete.
SCOTT@orcl_12.1.0.2.0> merge into province p
2 using (select distinct country_id, province_name
3 from staging, country
4 where staging.country_name = country.country_name) s
5 on (p.country_id = s.country_id and p.province_name = s.province_name)
6 when not matched then insert (p.country_id, p.province_name)
7 values (s.country_id, s.province_name)
8 /
7 rows merged.
SCOTT@orcl_12.1.0.2.0> commit
2 /
Commit complete.
SCOTT@orcl_12.1.0.2.0> merge into city c
2 using (select distinct province_id, city_name
3 from staging, country, province
4 where staging.country_name = country.country_name
5 and staging.province_name = province.province_name) s
6 on (c.province_id = s.province_id and c.city_name = s.city_name)
7 when not matched then insert (c.province_id, c.city_name)
8 values (s.province_id, s.city_name)
9 /
9 rows merged.
SCOTT@orcl_12.1.0.2.0> commit
2 /
Commit complete.
-- results:
SCOTT@orcl_12.1.0.2.0> select * from country
2 /
COUNTRY_ID COUNTRY_NAME
---------- -------------------------
1 BELGIUM
2 CANADA
3 Germany
3 rows selected.
SCOTT@orcl_12.1.0.2.0> select * from province
2 /
PROVINCE_ID COUNTRY_ID PROVINCE_NAME
----------- ---------- -------------------------
1 1 NAMUR
2 1 ANTVERP
3 2 QUEBEC
4 2 ALBERTA
5 2 ONTARIO
6 3 Berlin
7 3 Hamburg
7 rows selected.
SCOTT@orcl_12.1.0.2.0> select * from city
2 /
CITY_ID PROVINCE_ID CITY_NAME
---------- ----------- -------------------------
1 1 NAMUR
2 2 ANTVERP
3 3 MONTREAL
4 3 QUEBEC CITY
5 4 EDMONTON
6 5 TORONTO
7 6 Potsdam
8 7 Altona
9 7 Reinbek
9 rows selected.
SCOTT@orcl_12.1.0.2.0> select country_name, province_name, city_name
2 from country, province, city
3 where country.country_id = province.country_id
4 and province.province_id = city.province_id
5 order by 1, 2, 3
6 /
COUNTRY_NAME PROVINCE_NAME CITY_NAME
------------------------- ------------------------- -------------------------
BELGIUM ANTVERP ANTVERP
BELGIUM NAMUR NAMUR
CANADA ALBERTA EDMONTON
CANADA ONTARIO TORONTO
CANADA QUEBEC MONTREAL
CANADA QUEBEC QUEBEC CITY
Germany Berlin Potsdam
Germany Hamburg Altona
Germany Hamburg Reinbek
9 rows selected.
[Updated on: Sun, 08 July 2018 23:18] Report message to a moderator
|
|
|
Re: How can bulk insert into the parent , child tables [message #670516 is a reply to message #670473] |
Tue, 10 July 2018 05:30 |
|
fabi88
Messages: 112 Registered: November 2011
|
Senior Member |
|
|
@Solomon Yakobson,@Barbara Boehmer
Wooooow, Thank you so so so much, What great solutions. Your solutions have solved my problem. Thank you again for the time you spent on the solutions.
Quote:Too bad you refuse to post a test case I could SHOW you how to do it.
Sorry, It will be considered, Thank you for pointing out my mistake.
|
|
|
Goto Forum:
Current Time: Sat Sep 28 15:58:51 CDT 2024
|