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 Go to next message
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 #670474 is a reply to message #670473] Sun, 08 July 2018 01:36 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I would read the CSV file through an external table, and insert the rows with a multi-table insert. You could use error logging clauses to manage the duplicates.
Re: How can bulk insert into the parent , child tables [message #670475 is a reply to message #670474] Sun, 08 July 2018 01:56 Go to previous messageGo to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
Thank you so much for your useful reply.

Do you mean something like this?

INSERT ALL
INTO Countries(Country_Name)
VALUES (Country_Name)
INTO Provinces(Country_ID, Province_Name)
VALUES (?,Province_Name)
INTO Cities(Province_ID, City_Name)
VALUES (?,City_Name)
SELECT Country_Name, Province_Name, City_Name
From External_Table_Info

But how can get the inserted country_id, province_id and city_id?(Shown with question mark)
Re: How can bulk insert into the parent , child tables [message #670477 is a reply to message #670473] Sun, 08 July 2018 02:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: How can bulk insert into the parent , child tables [message #670478 is a reply to message #670475] Sun, 08 July 2018 03:55 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Identity columns.
Re: How can bulk insert into the parent , child tables [message #670480 is a reply to message #670478] Sun, 08 July 2018 06:44 Go to previous messageGo to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
Quote:

Identity columns.
Tables have identity column (country_id,province_id,city_id), but how can get inserted identity column value in a multi-table insert?
For example in individual insert :

INSERT INTO countries (country_name)
VALUES (country_name)
RETURNING country_id INTO tmp_country_id;


INSERT INTO provinces (country_id , province_name)
VALUES (tmp_country_id, province_name)
RETURNING province_id INTO tmp_province_id;


But as you know Oracle says Quote:
You cannot specify the returning_clause for a multitable insert.
Re: How can bulk insert into the parent , child tables [message #670481 is a reply to message #670480] Sun, 08 July 2018 07:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Too bad you refuse to post a test case I could SHOW you how to do it.

Re: How can bulk insert into the parent , child tables [message #670484 is a reply to message #670475] Sun, 08 July 2018 09:42 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
fabi88 wrote on Sun, 08 July 2018 02:56
Thank 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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous message
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.

Previous Topic: Report query
Next Topic: rownum from table
Goto Forum:
  


Current Time: Thu Mar 28 12:28:50 CDT 2024