<p>I'm looking for a good way to perform multi-row inserts into an Oracle 9 database.  The following works in MySQL but doesn't seem to be supported in Oracle.</p>

<pre><code>INSERT INTO TMP_DIM_EXCH_RT 
(EXCH_WH_KEY, 
 EXCH_NAT_KEY, 
 EXCH_DATE, EXCH_RATE, 
 FROM_CURCY_CD, 
 TO_CURCY_CD, 
 EXCH_EFF_DATE, 
 EXCH_EFF_END_DATE, 
 EXCH_LAST_UPDATED_DATE) 
VALUES
    (1, 1, '28-AUG-2008', 109.49, 'USD', 'JPY', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
    (2, 1, '28-AUG-2008', .54, 'USD', 'GBP', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
    (3, 1, '28-AUG-2008', 1.05, 'USD', 'CAD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
    (4, 1, '28-AUG-2008', .68, 'USD', 'EUR', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
    (5, 1, '28-AUG-2008', 1.16, 'USD', 'AUD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
    (6, 1, '28-AUG-2008', 7.81, 'USD', 'HKD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008');
</code></pre>

[{'answer_id': 39602, 'author': 'Espo', 'author_id': 2257, 'author_profile': 'https://Stackoverflow.com/users/2257', 'pm_score': 9, 'selected': True, 'text': "<p>This works in Oracle:</p>\n<pre><code>insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)\n          select 8000,0,'Multi 8000',1 from dual\nunion all select 8001,0,'Multi 8001',1 from dual\n</code></pre>\n<p>The thing to remember here is to use the <code>from dual</code> statement.</p>\n"}, {'answer_id': 39607, 'author': 'Ryan Ahearn', 'author_id': 75, 'author_profile': 'https://Stackoverflow.com/users/75', 'pm_score': 4, 'selected': False, 'text': '<p>If you have the values that you want to insert in another table already, then you can Insert from a select statement.</p>\n\n<pre><code>INSERT INTO a_table (column_a, column_b) SELECT column_a, column_b FROM b_table;\n</code></pre>\n\n<p>Otherwise, you can list a bunch of single row insert statements and submit several queries in bulk to save the time for something that works in both Oracle and MySQL.</p>\n\n<p><a href="https://stackoverflow.com/questions/39576/best-way-to-do-multi-row-insert-in-oracle#39602">@Espo</a>\'s solution is also a good one that will work in both Oracle and MySQL if your data isn\'t already in a table.</p>\n'}, {'answer_id': 41080, 'author': 'Matthew Watson', 'author_id': 3839, 'author_profile': 'https://Stackoverflow.com/users/3839', 'pm_score': 5, 'selected': False, 'text': '<p>Use SQL*Loader. It takes a little setting up, but if this isn\'t a one off, its worth it.</p>\n\n<p><strong>Create Table</strong></p>\n\n<pre><code>SQL&gt; create table ldr_test (id number(10) primary key, description varchar2(20));\nTable created.\nSQL&gt;\n</code></pre>\n\n<p><strong>Create CSV</strong></p>\n\n<pre><code>oracle-2% cat ldr_test.csv\n1,Apple\n2,Orange\n3,Pear\noracle-2% \n</code></pre>\n\n<p><strong>Create Loader Control File</strong></p>\n\n<pre><code>oracle-2% cat ldr_test.ctl \nload data\n\n infile \'ldr_test.csv\'\n into table ldr_test\n fields terminated by "," optionally enclosed by \'"\'              \n ( id, description )\n\noracle-2% \n</code></pre>\n\n<p><strong>Run SQL*Loader command</strong></p>\n\n<pre><code>oracle-2% sqlldr &lt;username&gt; control=ldr_test.ctl\nPassword:\n\nSQL*Loader: Release 9.2.0.5.0 - Production on Wed Sep 3 12:26:46 2008\n\nCopyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.\n\nCommit point reached - logical record count 3\n</code></pre>\n\n<p><strong>Confirm insert</strong></p>\n\n<pre><code>SQL&gt; select * from ldr_test;\n\n        ID DESCRIPTION\n---------- --------------------\n         1 Apple\n         2 Orange\n         3 Pear\n\nSQL&gt;\n</code></pre>\n\n<p>SQL*Loader has alot of options, and can take pretty much any text file as its input. You can even inline the data in your control file if you want.</p>\n\n<p>Here is a page with some more details -> <a href="http://www.orafaq.com/wiki/SQL%2ALoader_FAQ" rel="noreferrer">SQL*Loader</a></p>\n'}, {'answer_id': 91486, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 5, 'selected': False, 'text': "<p>Whenever I need to do this I build a simple PL/SQL block with a local procedure like this:</p>\n\n<pre><code>declare\n   procedure ins\n   is\n      (p_exch_wh_key INTEGER, \n       p_exch_nat_key INTEGER, \n       p_exch_date DATE, exch_rate NUMBER, \n       p_from_curcy_cd VARCHAR2, \n       p_to_curcy_cd VARCHAR2, \n       p_exch_eff_date DATE, \n       p_exch_eff_end_date DATE, \n       p_exch_last_updated_date DATE);\n   begin\n      insert into tmp_dim_exch_rt \n      (exch_wh_key, \n       exch_nat_key, \n       exch_date, exch_rate, \n       from_curcy_cd, \n       to_curcy_cd, \n       exch_eff_date, \n       exch_eff_end_date, \n       exch_last_updated_date) \n      values\n      (p_exch_wh_key, \n       p_exch_nat_key, \n       p_exch_date, exch_rate, \n       p_from_curcy_cd, \n       p_to_curcy_cd, \n       p_exch_eff_date, \n       p_exch_eff_end_date, \n       p_exch_last_updated_date);\n   end;\nbegin\n   ins (1, 1, '28-AUG-2008', 109.49, 'USD', 'JPY', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),\n   ins (2, 1, '28-AUG-2008', .54, 'USD', 'GBP', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),\n   ins (3, 1, '28-AUG-2008', 1.05, 'USD', 'CAD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),\n   ins (4, 1, '28-AUG-2008', .68, 'USD', 'EUR', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),\n   ins (5, 1, '28-AUG-2008', 1.16, 'USD', 'AUD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),\n   ins (6, 1, '28-AUG-2008', 7.81, 'USD', 'HKD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008');\nend;\n/\n</code></pre>\n"}, {'answer_id': 93724, 'author': 'Myto', 'author_id': 17827, 'author_profile': 'https://Stackoverflow.com/users/17827', 'pm_score': 9, 'selected': False, 'text': "<p>In Oracle, to insert multiple rows into table t with columns col1, col2 and col3 you can use the following syntax:</p>\n\n<pre><code>INSERT ALL\n   INTO t (col1, col2, col3) VALUES ('val1_1', 'val1_2', 'val1_3')\n   INTO t (col1, col2, col3) VALUES ('val2_1', 'val2_2', 'val2_3')\n   INTO t (col1, col2, col3) VALUES ('val3_1', 'val3_2', 'val3_3')\n   .\n   .\n   .\nSELECT 1 FROM DUAL;\n</code></pre>\n"}, {'answer_id': 50813275, 'author': 'Vasanth Raghavan', 'author_id': 4008963, 'author_profile': 'https://Stackoverflow.com/users/4008963', 'pm_score': -1, 'selected': False, 'text': '<p>Cursors may also be used, although it is inefficient. \nThe following stackoverflow post discusses the usage of cursors :</p>\n\n<p><a href="https://stackoverflow.com/questions/11921889/insert-and-update-a-record-using-cursors-in-oracle">INSERT and UPDATE a record using cursors in oracle</a></p>\n'}, {'answer_id': 53106217, 'author': 'Girdhar Singh Rathore', 'author_id': 5115670, 'author_profile': 'https://Stackoverflow.com/users/5115670', 'pm_score': 3, 'selected': False, 'text': "<p>you can insert using loop if you want to insert some random values.</p>\n\n<pre><code>BEGIN \n    FOR x IN 1 .. 1000 LOOP\n         INSERT INTO MULTI_INSERT_DEMO (ID, NAME)\n         SELECT x, 'anyName' FROM dual;\n    END LOOP;\nEND;\n</code></pre>\n"}, {'answer_id': 57430220, 'author': 'akasha', 'author_id': 2948162, 'author_profile': 'https://Stackoverflow.com/users/2948162', 'pm_score': -1, 'selected': False, 'text': '<p>Here is a very useful step by step guideline for insert multi rows in Oracle:</p>\n\n<p><a href="https://livesql.oracle.com/apex/livesql/file/content_BM1LJQ87M5CNIOKPOWPV6ZGR3.html" rel="nofollow noreferrer">https://livesql.oracle.com/apex/livesql/file/content_BM1LJQ87M5CNIOKPOWPV6ZGR3.html</a></p>\n\n<p>The last step:</p>\n\n<pre><code>INSERT ALL\n/* Everyone is a person, so insert all rows into people */\nWHEN 1=1 THEN\nINTO people (person_id, given_name, family_name, title)\nVALUES (id, given_name, family_name, title)\n/* Only people with an admission date are patients */\nWHEN admission_date IS NOT NULL THEN\nINTO patients (patient_id, last_admission_date)\nVALUES (id, admission_date)\n/* Only people with a hired date are staff */\nWHEN hired_date IS NOT NULL THEN\nINTO staff (staff_id, hired_date)\nVALUES (id, hired_date)\n  WITH names AS (\n    SELECT 4 id, \'Ruth\' given_name, \'Fox\' family_name, \'Mrs\' title,\n           NULL hired_date, DATE\'2009-12-31\' admission_date\n    FROM   dual UNION ALL\n    SELECT 5 id, \'Isabelle\' given_name, \'Squirrel\' family_name, \'Miss\' title ,\n           NULL hired_date, DATE\'2014-01-01\' admission_date\n    FROM   dual UNION ALL\n    SELECT 6 id, \'Justin\' given_name, \'Frog\' family_name, \'Master\' title,\n           NULL hired_date, DATE\'2015-04-22\' admission_date\n    FROM   dual UNION ALL\n    SELECT 7 id, \'Lisa\' given_name, \'Owl\' family_name, \'Dr\' title,\n           DATE\'2015-01-01\' hired_date, NULL admission_date\n    FROM   dual\n  )\n  SELECT * FROM names\n</code></pre>\n'}, {'answer_id': 58663131, 'author': 'java-addict301', 'author_id': 6501190, 'author_profile': 'https://Stackoverflow.com/users/6501190', 'pm_score': -1, 'selected': False, 'text': '<p>In my case, I was able to use a simple insert statement to bulk insert many rows into TABLE_A using just one column from TABLE_B and getting the other data elsewhere (sequence and a hardcoded value) :</p>\n\n<pre><code>INSERT INTO table_a (\n    id,\n    column_a,\n    column_b\n)\n    SELECT\n        table_a_seq.NEXTVAL,\n        b.name,\n        123\n    FROM\n        table_b b;\n</code></pre>\n\n<p>Result:</p>\n\n<pre><code>ID: NAME: CODE:\n1, JOHN, 123\n2, SAM, 123\n3, JESS, 123\n</code></pre>\n\n<p>etc</p>\n'}]
['https://Stackoverflow.com/questions/39576', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/3734/']