Re: Nested Table Query

From: The Magnet <art_at_unsu.com>
Date: Tue, 11 Sep 2012 08:54:27 -0700 (PDT)
Message-ID: <1ed98fcc-431c-42b7-ae55-85f4e6a3925f_at_l14g2000yqo.googlegroups.com>

On Sep 11, 10:32 am, ddf <orat..._at_msn.com> wrote:
> On Tuesday, September 11, 2012 8:09:00 AM UTC-6, The Magnet wrote:
> > Hi,
>
> > I'm trying to do a INSERT INTO (SELECT........
>
> > The table I am inserting into and the table I am selecting from have
>
> > the exact same layout.  However, both tables have nested tables
>
> > within.  I'm having such a hard time finding the right SQL syntax.
>
> > All the examples I am finding use literals to insert into the nested
>
> > table.  Here, I am trying to select from one table and insert into
>
> > another.
>
> > Table 1
>
> > ----------
>
> > Column 1
>
> > Column 2
>
> > Nested Table 1
>
> >    Nested Column 1
>
> >    Nested Column 2
>
> > Table 2
>
> > ----------
>
> > Column 1
>
> > Column 2
>
> > Nested Table 1
>
> >    Nested Column 1
>
> >    Nested Column 2
>
> > INSERT INTO table2
>
> >   SELECT t1.column1, t2.column2, n1.nested_column1, n1.nested_column2
>
> >   FROM table1 t1, TABLE(nested_table1) n1;
>
> > That did not work.   Can anyone help with the syntax?
>
> > Thanks!
>
> You're apparently trying too hard to get this done; if the two tables have the exact same layout (same column definitions and same nested table definitions) then it's easier than you  thought:
>
> SQL> CREATE TYPE people_typ AS OBJECT (
>   2             last_name      VARCHAR2(25),
>   3             department_id  NUMBER(4),
>   4             salary         NUMBER(8,2));
>   5  /
>
> Type created.
>
> SQL>
> SQL> CREATE TYPE people_tab_typ AS TABLE OF people_typ;
>   2  /
>
> Type created.
>
> SQL>
> SQL> CREATE TABLE hr_info (
>   2             department_id   NUMBER(4),
>   3             location_id     NUMBER(4),
>   4             manager_id      NUMBER(6),
>   5             people          people_tab_typ)
>   6     NESTED TABLE people STORE AS people_stor_tab;
>
> Table created.
>
> SQL>
> SQL> CREATE TABLE hr_info_cpy (
>   2             department_id   NUMBER(4),
>   3             location_id     NUMBER(4),
>   4             manager_id      NUMBER(6),
>   5             people          people_tab_typ)
>   6     NESTED TABLE people STORE AS people_stor_tab_cpy;
>
> Table created.
>
> SQL>
> SQL> INSERT INTO hr_info VALUES (280, 1800, 999, people_tab_typ());
>
> 1 row created.
>
> SQL>
> SQL> INSERT INTO TABLE(SELECT h.people FROM hr_info h
>   2             WHERE h.department_id = 280)
>   3     VALUES ('Smith', 280, 1750);
>
> 1 row created.
>
> SQL>
> SQL> commit;
>
> Commit complete.
>
> SQL>
> SQL> select * from hr_info;
>
> DEPARTMENT_ID LOCATION_ID MANAGER_ID
> ------------- ----------- ----------
> PEOPLE(LAST_NAME, DEPARTMENT_ID, SALARY)
> --------------------------------------------------------------------------------
>           280        1800        999
> PEOPLE_TAB_TYP(PEOPLE_TYP('Smith', 280, 1750))
>
> SQL>
> SQL> insert into hr_info_cpy
>   2  select * from hr_info;
>
> 1 row created.
>
> SQL>
> SQL> select * From hr_info_cpy;
>
> DEPARTMENT_ID LOCATION_ID MANAGER_ID
> ------------- ----------- ----------
> PEOPLE(LAST_NAME, DEPARTMENT_ID, SALARY)
> --------------------------------------------------------------------------------
>           280        1800        999
> PEOPLE_TAB_TYP(PEOPLE_TYP('Smith', 280, 1750))
>
> SQL>
>
> David Fitzjarrell

David,

Your solution worked! Thanks. I'm just not clear on one thing: The tables had the same exact layout. The only difference being that I created separate types for the source and destination table.

In your code you use the same types, but a different STORE AS clause. I'm not sure I understand how Oracle is seeing them as different. Aside from the fact they are different objects, the layouts are the same....... Received on Tue Sep 11 2012 - 10:54:27 CDT