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 ammount from the nested table(prod_an) into year and ammount 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?
sql database oracle plsql
asked Jan 3 at 0:09
atf01atf01
3016
add a comment |
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 ammount from the nested table(prod_an) into year and ammount 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?
sql database oracle plsql
asked Jan 3 at 0:09
atf01atf01
3016
Don't useyearas a column name, it's an Oracle keyword and your code will become confusing very quickly. Also, if you meant to use English words, please note that amount is spelled with a single m.
– mathguy
Jan 3 at 2:50
1
@mathguy yes I'm aware, I just changed all my variable/attribute/table names to english to make it easier to understand for most people here as english is not my native language, thank you for the corrections.
– atf01
Jan 3 at 14:43
add a comment |
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 ammount from the nested table(prod_an) into year and ammount 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?
sql database oracle plsql
asked Jan 3 at 0:09
atf01atf01
3016
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 ammount from the nested table(prod_an) into year and ammount 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?
sql database oracle plsql
sql database oracle plsql
asked Jan 3 at 0:09
atf01atf01
3016
asked Jan 3 at 0:09
atf01atf01
3016
edited Jan 3 at 14:44
atf01
asked Jan 3 at 0:09
atf01atf01
3016
asked Jan 3 at 0:09
atf01atf01
3016
asked Jan 3 at 0:09
atf01atf01
3016
3016
Don't useyearas a column name, it's an Oracle keyword and your code will become confusing very quickly. Also, if you meant to use English words, please note that amount is spelled with a single m.
– mathguy
Jan 3 at 2:50
1
@mathguy yes I'm aware, I just changed all my variable/attribute/table names to english to make it easier to understand for most people here as english is not my native language, thank you for the corrections.
– atf01
Jan 3 at 14:43
add a comment |
Don't useyearas a column name, it's an Oracle keyword and your code will become confusing very quickly. Also, if you meant to use English words, please note that amount is spelled with a single m.
– mathguy
Jan 3 at 2:50
1
@mathguy yes I'm aware, I just changed all my variable/attribute/table names to english to make it easier to understand for most people here as english is not my native language, thank you for the corrections.
– atf01
Jan 3 at 14:43
Don't use
year as a column name, it's an Oracle keyword and your code will become confusing very quickly. Also, if you meant to use English words, please note that amount is spelled with a single m.– mathguy
Jan 3 at 2:50
Don't use
year as a column name, it's an Oracle keyword and your code will become confusing very quickly. Also, if you meant to use English words, please note that amount is spelled with a single m.– mathguy
Jan 3 at 2:50
1
1
@mathguy yes I'm aware, I just changed all my variable/attribute/table names to english to make it easier to understand for most people here as english is not my native language, thank you for the corrections.
– atf01
Jan 3 at 14:43
@mathguy yes I'm aware, I just changed all my variable/attribute/table names to english to make it easier to understand for most people here as english is not my native language, thank you for the corrections.
– atf01
Jan 3 at 14:43
add a comment |
1 Answer
1
active
oldest
votes
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,
ammount,
country_fk)
SELECT p.year,
p.ammount,
c.id
FROM country c
CROSS JOIN TABLE(c.prod_an) p;
answered Jan 3 at 2:36
sticky bitsticky bit
15.1k101733
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54014801%2fhow-can-i-insert-values-from-a-nested-table-into-another-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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,
ammount,
country_fk)
SELECT p.year,
p.ammount,
c.id
FROM country c
CROSS JOIN TABLE(c.prod_an) p;
answered Jan 3 at 2:36
sticky bitsticky bit
15.1k101733
add a comment |
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,
ammount,
country_fk)
SELECT p.year,
p.ammount,
c.id
FROM country c
CROSS JOIN TABLE(c.prod_an) p;
answered Jan 3 at 2:36
sticky bitsticky bit
15.1k101733
add a comment |
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,
ammount,
country_fk)
SELECT p.year,
p.ammount,
c.id
FROM country c
CROSS JOIN TABLE(c.prod_an) p;
answered Jan 3 at 2:36
sticky bitsticky bit
15.1k101733
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,
ammount,
country_fk)
SELECT p.year,
p.ammount,
c.id
FROM country c
CROSS JOIN TABLE(c.prod_an) p;
answered Jan 3 at 2:36
sticky bitsticky bit
15.1k101733
answered Jan 3 at 2:36
sticky bitsticky bit
15.1k101733
answered Jan 3 at 2:36
sticky bitsticky bit
15.1k101733
answered Jan 3 at 2:36
sticky bitsticky bit
15.1k101733
15.1k101733
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54014801%2fhow-can-i-insert-values-from-a-nested-table-into-another-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Don't use
yearas a column name, it's an Oracle keyword and your code will become confusing very quickly. Also, if you meant to use English words, please note that amount is spelled with a single m.– mathguy
Jan 3 at 2:50
1
@mathguy yes I'm aware, I just changed all my variable/attribute/table names to english to make it easier to understand for most people here as english is not my native language, thank you for the corrections.
– atf01
Jan 3 at 14:43