Inserting multiple rows in Oracle 10g

20 pts.
Tags:
INSERT statement
Oracle
SQL
I need to insert multiple rows of data into multiple tables from one temporary table. I need to put some of the columns into one, some into another, and the remainder into another table. I realize I will need to create INSERT statements for each table insert. Is there an easy way to select specific columns from the temporary table and load them into the target table? Here is an example of one statement I tried that received errors.
INSERT INTO iterm.coded_concept (coded_concept_id, code) VALUES (iterm.coded_concept_seq.nextval, SELECT loinc_num FROM iterm.loinc_temp_data);
Any advice would be appreciated. Thank-you.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Actually, Oracle has an “INSERT ALL” statement that I believe was added in 9i and is available in 10g (someone please correct this answer if I’m wrong on that!)

It’s a pretty powerful statement that allows you to insert into multiple tables at once by performing a select from your temp table of all the columns you need, and inserting each column’s values into whichever of the target tables you specify.

The basic syntax is something like
<pre>
INSERT ALL
INTO table1 VALUES(val_A, val_B)
INTO table2 VALUES(val_C, val_D)
SELECT tmp_a val_A, tmp_b val_B, tmp_c val_C, tmp_d val_D
FROM tmp_table WHERE …
</pre>
This will insert multiple rows (any matching the where clause of the select) into both table1 and table2.

More info from an <a href=”http://www.oracle-base.com/articles/9i/SQLNewFeatures9i.php#MultitableInserts”>oracle article</a>.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Ora mer
    [...] Address: http://itknowledgeexchange.techtarget.com/itanswers/inserting-multiple-rows-in-oracle-10g/ (0) Comments Read [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following