Лучший способ сделать многострочную вставку в Oracle?
Я ищу хороший способ выполнения многострочных вставок в базу данных Oracle 9. Следующее работает в MySQL, но, похоже, не поддерживается в Oracle.
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');
11 ответов
Это работает в 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 dual заявление.
( источник)
В 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;
Используйте SQL*Loader. Требуется небольшая настройка, но если это не единственный, это того стоит.
Создать таблицу
SQL> create table ldr_test (id number(10) primary key, description varchar2(20));
Table created.
SQL>
Создать CSV
oracle-2% cat ldr_test.csv
1,Apple
2,Orange
3,Pear
oracle-2%
Создать файл управления загрузчиком
oracle-2% cat ldr_test.ctl
load data
infile 'ldr_test.csv'
into table ldr_test
fields terminated by "," optionally enclosed by '"'
( id, description )
oracle-2%
Запустите команду SQL * Loader
oracle-2% sqlldr <username> control=ldr_test.ctl
Password:
SQL*Loader: Release 9.2.0.5.0 - Production on Wed Sep 3 12:26:46 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 3
Подтвердите вставку
SQL> select * from ldr_test;
ID DESCRIPTION
---------- --------------------
1 Apple
2 Orange
3 Pear
SQL>
SQL * Loader имеет множество опций и может принимать практически любой текстовый файл в качестве входных данных. Вы можете даже вставить данные в свой контрольный файл, если хотите.
Вот страница с более подробной информацией -> SQL * Loader
Всякий раз, когда мне нужно сделать это, я создаю простой блок PL/SQL с локальной процедурой, подобной этой:
declare
procedure ins
is
(p_exch_wh_key INTEGER,
p_exch_nat_key INTEGER,
p_exch_date DATE, exch_rate NUMBER,
p_from_curcy_cd VARCHAR2,
p_to_curcy_cd VARCHAR2,
p_exch_eff_date DATE,
p_exch_eff_end_date DATE,
p_exch_last_updated_date DATE);
begin
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
(p_exch_wh_key,
p_exch_nat_key,
p_exch_date, exch_rate,
p_from_curcy_cd,
p_to_curcy_cd,
p_exch_eff_date,
p_exch_eff_end_date,
p_exch_last_updated_date);
end;
begin
ins (1, 1, '28-AUG-2008', 109.49, 'USD', 'JPY', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
ins (2, 1, '28-AUG-2008', .54, 'USD', 'GBP', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
ins (3, 1, '28-AUG-2008', 1.05, 'USD', 'CAD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
ins (4, 1, '28-AUG-2008', .68, 'USD', 'EUR', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
ins (5, 1, '28-AUG-2008', 1.16, 'USD', 'AUD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
ins (6, 1, '28-AUG-2008', 7.81, 'USD', 'HKD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008');
end;
/
Если у вас уже есть значения, которые вы хотите вставить в другую таблицу, вы можете вставить из оператора select.
INSERT INTO a_table (column_a, column_b) SELECT column_a, column_b FROM b_table;
В противном случае вы можете перечислить несколько операторов вставки в одну строку и отправить несколько запросов, чтобы сэкономить время на то, что работает как в Oracle, так и в MySQL.
Решение @Espo также является хорошим решением, которое будет работать как в Oracle, так и в MySQL, если ваших данных еще нет в таблице.
Вы можете вставить, используя цикл, если вы хотите вставить некоторые случайные значения.
BEGIN
FOR x IN 1 .. 1000 LOOP
INSERT INTO MULTI_INSERT_DEMO (ID, NAME)
SELECT x, 'anyName' FROM dual;
END LOOP;
END;
Интересно, будет ли вариант простого пакетного оператора в анонимном блоке:
create table foo(id int);
begin
insert into foo(id) values (1);
insert into foo(id) values (2);
end;
/
Источник:
Запрос функции db<>fiddle/Top Answers — поддержка Oracle в dbfiddle не позволяет использовать несколько операторов в одном пакете
Курсоры также могут быть использованы, хотя это неэффективно. В следующем посте stackru обсуждается использование курсоров:
Вот очень полезное пошаговое руководство для вставки нескольких строк в 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
insert all
into table_name (columns list) values(values list)
into table_name (columns list) values(values list)
.......
.......
into table_name (columns list) values(values list)
select * from dual;
В моем случае я смог использовать простой оператор вставки для массовой вставки множества строк в 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
так далее