I want to grab values from a nested table in one table and insert said values into another table Here's the type for the nested table: The nested table: Here's the table that contains the nested table: Here's the table into which I want to insert What I want to do is I want to grab the values from prod_an

How can I insert values from a nested table into another table?

I want to grab values from a nested table in one table and insert said values into another table

Here’s the type for the nested table:

CREATE OR REPLACE TYPE type_val AS OBJECT 
                       (
                            year   DATE,
                            amount INTEGER
                       );

The nested table:

CREATE OR REPLACE TYPE nt_type_val IS
TABLE OF type_val;

Here’s the table that contains the nested table:

CREATE TABLE country 
(
    id        INTEGER NOT NULL,
    name      VARCHAR2(100) NOT NULL,
    continent VARCHAR2(30) NOT NULL,
    prod_an   nt_type_val 
)
NESTED TABLE prod_an STORE AS nt_prod_an;

Here’s the table into which I want to insert

CREATE TABLE prod_country_ai
(
    year       DATE NOT NULL,
    amount     INTEGER NOT NULL,
    country_fk INTEGER NOT NULL
)

What I want to do is I want to grab the values from prod_an in the country table for each country and store them in the prod_country_ai table, respectively, year and amount from the nested table (prod_an) into year and amount on prod_country_ai and the primary key from country into country_fk on prod_country_ai.

I have the following piece for a procedure that would do that:

DECLARE
CURSOR inner_table IS
 SELECT  t.* FROM country p, TABLE(p.prod_an) t 
 WHERE p.name = 'Portugal';
BEGIN
  FOR i IN inner_table LOOP
     dbms_output.put_line( i.year || i.quantity);
    END LOOP;
END;

This successfully outputs the year followed by the amount but it only does so upon specification of the country name, the solution I thought of is running an “outer loop” that cycles on the country table (could be by id or by country name it doesn’t change much because each value will be unique either way), and I’m guessing I can use i.year and i.quantity directly on an insert statement inside the “inner loop” to insert into prod_country_ai, but I’m not sure how I can do this, also, I think variables are treated as “local” inside a loop so how could I go about inserting the country primary key as a foreign key in the prod_country_ai table?

Answer

You don’t need a procedure for this. You can do this with an INSERT ... SELECT from the countries cross joining the nested tables.

INSERT INTO prod_country_ai (year, amount, country_fk)
    SELECT 
        p.year, p.amount, c.id
    FROM 
        country c
    CROSS JOIN TABLE(c.prod_an) p;

Advertisement

Answer