在我的例子中，我能够使用一个简单的插入语句批量插入许多行到TABLE_A中，只使用TABLE_B中的一列，并在其他地方获取其他数据(序列和硬编码的值):

INSERT INTO table_a (
    id,
    column_a,
    column_b
)
    SELECT
        table_a_seq.NEXTVAL,
        b.name,
        123
    FROM
        table_b b;

结果:

ID: NAME: CODE:
1, JOHN, 123
2, SAM, 123
3, JESS, 123

etc

这在Oracle中是有效的:

insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
          select 8000,0,'Multi 8000',1 from dual
union all select 8001,0,'Multi 8001',1 from dual

这里要记住的是使用from对偶语句。

在Oracle中，要向表t插入多行，列为col1、col2和col3，可以使用以下语法:

INSERT ALL
   INTO t (col1, col2, col3) VALUES ('val1_1', 'val1_2', 'val1_3')
   INTO t (col1, col2, col3) VALUES ('val2_1', 'val2_2', 'val2_3')
   INTO t (col1, col2, col3) VALUES ('val3_1', 'val3_2', 'val3_3')
   .
   .
   .
SELECT 1 FROM DUAL;

下面是一个非常有用的在Oracle中插入多行的指南:

https://livesql.oracle.com/apex/livesql/file/content_BM1LJQ87M5CNIOKPOWPV6ZGR3.html

最后一步:

INSERT ALL
/* Everyone is a person, so insert all rows into people */
WHEN 1=1 THEN
INTO people (person_id, given_name, family_name, title)
VALUES (id, given_name, family_name, title)
/* Only people with an admission date are patients */
WHEN admission_date IS NOT NULL THEN
INTO patients (patient_id, last_admission_date)
VALUES (id, admission_date)
/* Only people with a hired date are staff */
WHEN hired_date IS NOT NULL THEN
INTO staff (staff_id, hired_date)
VALUES (id, hired_date)
  WITH names AS (
    SELECT 4 id, 'Ruth' given_name, 'Fox' family_name, 'Mrs' title,
           NULL hired_date, DATE'2009-12-31' admission_date
    FROM   dual UNION ALL
    SELECT 5 id, 'Isabelle' given_name, 'Squirrel' family_name, 'Miss' title ,
           NULL hired_date, DATE'2014-01-01' admission_date
    FROM   dual UNION ALL
    SELECT 6 id, 'Justin' given_name, 'Frog' family_name, 'Master' title,
           NULL hired_date, DATE'2015-04-22' admission_date
    FROM   dual UNION ALL
    SELECT 7 id, 'Lisa' given_name, 'Owl' family_name, 'Dr' title,
           DATE'2015-01-01' hired_date, NULL admission_date
    FROM   dual
  )
  SELECT * FROM names

游标也可以使用，尽管它效率很低。 下面的stackoverflow帖子讨论了游标的用法:

在oracle中使用游标插入和更新记录

在我的例子中，我能够使用一个简单的插入语句批量插入许多行到TABLE_A中，只使用TABLE_B中的一列，并在其他地方获取其他数据(序列和硬编码的值):

INSERT INTO table_a (
    id,
    column_a,
    column_b
)
    SELECT
        table_a_seq.NEXTVAL,
        b.name,
        123
    FROM
        table_b b;

结果:

ID: NAME: CODE:
1, JOHN, 123
2, SAM, 123
3, JESS, 123

etc