Skip to content
Advertisement

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:

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 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:

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?

Advertisement

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.

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement