2 Replies Latest reply: Dec 19, 2013 2:15 AM by Richard Harrison . RSS

    insert multiple rows into a same table from a single record

    972590

      Hi All,

       

      I need to insert multiple rows into a same table from a single record. Here is what I am trying to do and I need your expertise. I am using Oracle 11g

       

      DataFile

      1,"1001,2001,3001,4001"

      2,"1002,2002,3002,4002"

       

      The data needs to be loaded as

       

      Field1      Field2

      1               1001

      1               2001

      1               3001

      1               4001

      2               1002

      2               2002

      2               3002

      2               4002

       

      Thanks

        • 1. Re: insert multiple rows into a same table from a single record
          Barbara Boehmer

          You could use SQL*Loader to load the data into a staging table with a varray column, then use a SQL insert statement to distribute it to the destination table, as demonstrated below.

           

           

          SCOTT@orcl> host type test.dat

          1,"1001,2001,3001,4001"

          2,"1002,2002,3002,4002"

           

          SCOTT@orcl> host type test.ctl

          load data

          infile test.dat

          into table staging

          fields terminated by ','

          ( field1

          , numbers varray enclosed by '"' and '"' (x))

           

          SCOTT@orcl> create table staging

            2    (field1  number,

            3     numbers sys.odcinumberlist)

            4  /

           

          Table created.

           

          SCOTT@orcl> host sqlldr scott/tiger control=test.ctl log=test.log

           

          SQL*Loader: Release 11.2.0.1.0 - Production on Wed Dec 18 21:48:09 2013

           

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

           

          Commit point reached - logical record count 2

           

          SCOTT@orcl> column numbers format a60

          SCOTT@orcl> select * from staging

            2  /

           

              FIELD1 NUMBERS

          ---------- ------------------------------------------------------------

                   1 ODCINUMBERLIST(1001, 2001, 3001, 4001)

                   2 ODCINUMBERLIST(1002, 2002, 3002, 4002)

           

          2 rows selected.

           

          SCOTT@orcl> create table destination

            2    (field1  number,

            3     field2  number)

            4  /

           

          Table created.

           

          SCOTT@orcl> insert into destination

            2  select s.field1, t.column_value

            3  from   staging s, table (s.numbers) t

            4  /

           

          8 rows created.

           

          SCOTT@orcl> select * from destination

            2  /

           

              FIELD1     FIELD2

          ---------- ----------

                   1       1001

                   1       2001

                   1       3001

                   1       4001

                   2       1002

                   2       2002

                   2       3002

                   2       4002

           

          8 rows selected.

          • 2. Re: insert multiple rows into a same table from a single record
            Richard Harrison .

            Hi,

            I'd switch to using an external table rather than sqlldr directly and then use a multi table insert from this external table to the destination tables as described here: ORACLE-BASE - Oracle 9i SQL New Features

             

            Regards,

            Harry