Re: Nested Table Query

From: ddf <oratune_at_msn.com>
Date: Tue, 11 Sep 2012 08:32:11 -0700 (PDT)
Message-ID: <aa91b2f6-ee42-44e9-a9b8-bd28e42791f4_at_googlegroups.com>

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 Received on Tue Sep 11 2012 - 10:32:11 CDT