Вставить несколько строк oracle
Обновлено: 03.07.2024
В дискуссии о вставке нескольких строк в Oracle были продемонстрированы два подхода:
Может ли кто-нибудь утверждать о предпочтении использования одного над другим?
PS Я не проводил никаких исследований сам (даже план объяснения), поэтому любая информация или мнение будут оценены.
С точки зрения производительности эти запросы идентичны.
UNION ALL не повредит производительности, так как Oracle оценивает UNION 'запрос, только когда это необходимо, сначала не кэширует результаты.
SELECT синтаксис является более гибким в том смысле, что вы можете более легко манипулировать SELECT запрос, если вы хотите что-то изменить.
Например, этот запрос:
может быть переписан как
Заменяя 2 с соответствующим номером, вы можете получить любое количество строк, которое вы хотите.
В случае INSERT ALL , вам придется дублировать описание таблицы назначения, которое будет менее читабельным, если вам нужно, скажем, 40 строк.
INSERT ALL Метод имеет проблему с вставкой большего количества строк в таблицу.
Недавно я хотел вставить 1130 строк в таблицу с одним оператором SQL. Когда я пытался сделать это с INSERT ALL Метод я получил следующую ошибку:
ORA-24335 - не может поддерживать более 1000 столбцов
Когда я использовал INSERT INTO .. UNION ALL .. подойти все прошло нормально.
Btw. Я не знал о методе UNION ALL, пока не нашел это обсуждение:)
Я подозреваю, что решение 1 - это хак, который работает и, вероятно, менее эффективен, чем разработанная альтернатива Insert ALL.
"Вставить все" действительно предназначен для вставки множества строк в более чем 1 таблицу в результате выбора, например:
Если вы хотите загрузить тысячи строк, а их еще нет в базе данных, я не думаю, что это лучший способ сделать это - если ваши данные находятся в файле, вы хотите посмотреть на внешние таблицы или загрузчик SQL, чтобы эффективно вставить строки для вас.
Я попробовал какой-то тест, и более быстрое решение должно быть
буферизация между 300 400 строками (я пробовал с odbc, это значение может зависеть от его конфигурации)
Заявление с использованием UNION ALL теоретически имеет небольшой недостаток производительности, так как он должен объединить результаты всех операторов, прежде чем может произойти вставка. INSERT ALL не имеет этого недостатка, так как конечный результат уже может быть обработан построчно.
Но практически оптимизатор в Oracle должен сделать разницу незначительной, и ваш выбор зависит от ваших предпочтений.
По моему мнению, INSERT ALL является лучшим для восприятия человеком, в то время как UNION ALL вариант - тот, который занимает меньше места, когда такая вставка генерируется автоматически.
SQL оператор INSERT используется для вставки записей в существующую таблицу.
Синтаксис этого оператора следующий:
Создадим тестовую таблицу
Давайте создадим таблицу table1 со столбцами a, b, c в нашей MySQL базе данных:
Запрос на вставку строки
Простой запрос, который вставляет строку со столбцами 111, 222 и 333 выглядит так:
Еще один способ сделать то же самое:
Столбцы, которые вы не перечислите заполняются значениями по умолчанию, которые вы предусматриваете при создании таблицы, даже если это просто NULL.
У таблиц обычно есть поле id с первичным ключом (PRIMARY KEY) таблицы. Если этому полю установлено значение AUTOINCREMENT т.е. оно заполняется автоматически, то в таком случае вы не должны его перечислять в списке столбцов оператора INSERT.
Вставка без перечисления столбцов
Если количество значений, которые мы вставляем = количеству столбцов в таблице, то можно не перечислять столбцы, и наш запрос может выглядеть так:
Этот способ крайне не рекомендуется. Дело в том, что со временем вы можете менять таблицы, например добавлять в них новые столбцы, а это значит, что все запросы записанные таким способом просто перестанут работать и вам придется менять их по всему вашему приложению. Поэтому, навсегда забываем этот способ. Я его привел, только чтобы вы так не делали.
Вставка сразу нескольких строк с помощью INSERT INTO
Если нам нужно вставить несколько строк, то мы просто перечисляем группы значений через запятую выглядит это так:
Таким образом мы вставили 3 строки в нашу таблицу table1. Их может быть и больше. В MySQL четкого предела нет, однако он все таки существует и зависит от параметра max_allowed_packet который ограничивает размер запроса. Если вы установите SET GLOBAL max_allowed_packet=524288000; то размер запроса будет ограничен 500MB но делайте это в очень крайнем случае. Обычно всегда можно найти решение и разделить 1 большой запрос, на несколько более мелких и вставлять например не больше 1000 строк за один цикл.
Как вставить значение из другой таблицы INSERT INTO . SELECT .
Допустим у нас есть еще одна таблица table2 которая по структуре точно такая же как и первая. Нам в таблицу table2 нужно вставить все строки из table1.
Вставляем значения из table1 в таблицу table2:
Если вы делаете не какую-то единичную вставку при переносе данных, а где-то сохраните этот запрос, например в вашем PHP скрипте, то всегда перечисляйте столбцы.
Как не рекомендуется делать (без перечисления столбцов):
Если у вас со временем изменится количество столбцов в таблице, то запрос перестанет работать. При выполнении запроса MySQL в лучшем случае просто будет возвращать ошибку:
Либо еще хуже: значения вставятся не в те столбцы.
Вставка из другой таблицы с условием INSERT INTO . SELECT . WHERE .
А теперь представим, что нам нужно вставить только те строки из table1, у которых столбец "c" равен 333. Тогда наш запрос будет выглядеть так
То есть мы просто вставляем данные в таблицу, которые выбрали из другой таблицы при помощи обычного SELECT запроса
Теперь представим, что у нас в таблице table2 - 4 столбца, а в table1 - 3. При этом четвертый столбец в table2 обязательный. Чтобы выйти из этой ситуации, нужно передать какое-нибудь подходящее значение в этот лишний столбец. У нас чисто абстрактная задача, поэтому давайте передадим туда просто единицу.
Теперь в столбец d у нас записалась единица и проблема решена.
Вставка в определенный раздел INSERT INTO . PARTITION .
Если вам нужно вставить строки в определенный раздел таблицы, то нужно после таблицы указать PARTITION (название раздела), например так:
Вставка в несколько разделов. Первая строка вставляется в раздел p1, а вторая в p2
Вставка строк, некоторые из которых уже существуют в целевой таблице
Существование строк определяется по значению уникальных ключей. В зависимости от ситуации мы можем выбрать разные способы поведения при совпадении значений уникальных столбцов.
Игнорирование INSERT IGNORE INTO
Например если мы вставляем строку с PK = 1, и при этом в таблице уже есть PK = 1 то MySQL выдаст ошибку:
Выполнение запроса на этом прервется, однако нам в некоторых случаях хотелось бы просто вставить данные, игнорируя ошибки. В этом нам поможет INSERT IGNORE INTO:
Просто добавляем IGNORE в наш запрос и ошибки будут игнорироваться
Вставка с заменой существующих значений REPLACE INTO
REPLACE работает также INSERT, но если совпадают уникальные ключи, то старая строка (или строки!) удаляется до вставки новой.
В таком случае наш пример выглядит следующим образом:
Обновление некоторых полей, при существовании строк ON DUPLICATE KEY UPDATE
При совпадении ключей, мы можем также заменить некоторые или все поля в строке.
Наш запрос будет выглядеть так:
В данном примере если у нас какой-то уникальный ключ совпадает, то мы не производим вставку, а обновляем существующую строку или строки путем присваивания столбцу "c" значения, которое у нас перечислено в VALUES.
Иными словами, если ключ совпадает, то мы просто обновим данные столбца "с" а остальные столбцы трогать не будем.
Иногда нам нужно при совпадении ключей обновить все значения. Этом можно сделать просто перечислив все столбцы:
При обновлении столбцов мы также можем использовать разные выражения, например:
Выражения для вставляемых значений в VALUES
При вставке значений, мы можем использовать выражения и даже использовать в своих выражениях значения других столбцов.
Пример использования выражений:
Таким образом мы для формирования столбца "c" использовали столбцы "a" и "b".
Приоритет вставки INSERT LOW_PRIORITY / HIGH_PRIORITY
Установление приоритета нужно для решение проблем с конкурентными вставками. При вставках происходит блокировка строк и если 2 INSERT запроса требуют блокировки одних и тех же строк, для своего выполнения, то иногда может потребоваться повысить или понизить приоритет некоторых запросов, по отношению к другим. Это можно сделать указав приоритет LOW_PRIORITY или HIGH_PRIORITY
SQL * У загрузчика много опций , и может принимать в значительной степени любой текстовый файл в качестве его ввода. Вы можете даже встроить данные в управляющий файл, если хотите.
Вот страница с более подробной информацией -> SQL * Loader
Существует также что-то, называемое «Вставить все». от 9i (?) – mlathe 11 November 2010 в 20:30 Это должен быть главный ответ ИМХО, что-нибудь еще (для крупномасштабных задач) задает проблемы – ropata 31 May 2016 в 12:45 Быть разборчивым, но форматирование имеет смысл, если вы ставите «объединение всех», в конце каждой строки выбора (кроме последней). – Jamie 25 April 2017 в 20:34 Одним из недостатков этого является то, что мы не можем использовать sequnce.nextval , как это запрещено в union из select . Вместо этого мы можем пойти с INSERT ALL . – sql_dummy 8 May 2017 в 02:41 @Jamie: форматирование Espo немного умнее в том смысле, что вам не нужно беспокоиться о том, находитесь ли вы на последней строке или нет, добавляя новые строки. Следовательно, как только у вас есть ваши первые 2 выбора, вы можете легко скопировать / вставить последнюю строку (или среднюю), только сосредоточив внимание на значениях, которые вы должны изменить. Это общий трюк для множества других случаев на любых языках (запятая, логические операторы плюс . ). Это просто привычка, многие из прежних практик были пересмотрены, чтобы сосредоточиться на ответственности кода больше, чем на интуиции. – Laurent.B 24 May 2017 в 08:19 Столбец ID моей таблицы автогенерируется. Можно ли просто пропустить поле ID в файле управления загрузчиком? – Thom DeCarlo 8 September 2017 в 14:18 @Thom, используйте последовательность.nextval, например. fruit_id "fruit_seq.nextval" в определении столбца – ropata 8 February 2018 в 07:07 50 миллионов записей за несколько минут. Способ передвижения – Toolkit 29 May 2018 в 17:14Если у вас есть значения, которые вы хотите вставить в другую таблицу уже, то вы можете вставить из оператора select.
В противном случае вы можете перечислить кучу одноразовых вставных операторов и отправьте несколько запросов навалом, чтобы сэкономить время на то, что работает как в Oracle, так и в MySQL.
Решение @Espo также является хорошим, которое будет работать как в Oracle, так и в Oracle MySQL, если ваши данные еще не находятся в таблице.
В Oracle, чтобы вставить несколько строк в таблицу t с столбцами col1, col2 и col3, вы можете использовать следующий синтаксис:
Я не понимаю, что делает SELECT 1 FROM DUAL . – jameshfisher 28 March 2013 в 14:23 INSERT ALL требует подзапроса SELECT . Чтобы обойти это, SELECT 1 FROM DUAL используется, чтобы дать одну строку фиктивных данных. – Markus Jarderot 25 June 2013 в 09:17 Как это отличается от нескольких заявлений вставки? У вас все еще есть повторение по именам столбцов, поэтому, похоже, они не очень много. – Burhan Ali 21 March 2014 в 14:32 Примерно 10-12. Несколько операторов INSERT завершаются за 2 секунды на моем ПК, в то время как вышеприведенный синтаксис способен вставить 1000 записей в секунду! Впечатлительный! Обратите внимание, что я COMMIT только в конце. – Kent Pawar 22 April 2014 в 22:30 Это прекрасно работает, однако, если вы вставляете последовательность, скажем, user.NEXTVAL, она вернет одинаковое значение для каждой вставки. Вы можете вручную увеличить его во вставке, а затем обновить последовательность вне вставки. – user1412523 3 December 2015 в 13:03Всякий раз, когда мне это нужно, я строю простой блок PL / SQL с локальной процедурой, подобной этой:
В первой части статьи была рассмотрена работа многотабличной безусловной команды INSERT . Продолжим знакомство с командой INSERT , вставляющей строки одновременно в несколько таблиц, и поэкспериментируем с условным вариантом этой команды.
В многотабличных условных предложениях INSERT одной или нескольким частям INTO предшествует условие WHEN , которое и определяет, будут ли выполнены следующие за ним части INTO для каждой строки, возвращенной подзапросом.
INSERT ALL означает, что нужно проверить каждое из условий WHEN, независимо от того, оказались истинными или ложными предыдущие условия. Таким образом, одна строка, возвращенная подзапросом, может удовлетворить несколько условий WHEN и привести к выполнению INTO , соответствующих каждому из условий.
INSERT FIRST означает, что для строки, возвращенной подзапросом, выполнятся только части INTO , связанные с первым условием WHEN , которое окажется истинным. Оставшиеся условия WHEN проверяться не будут.
Опциональная часть ELSE может содержать альтернативные части INTO , которые выполнятся, если ни одно из условий WHEN не сработает.
Для экспериментов вновь воспользуемся системным вью all_users . Создадим несколько пустых таблиц, в которые будем вставлять строки, извлеченные из all_users , затем выполним INSERT :
Приведенная команда INSERT вставляет записи, возвращенные подзапросом, в те или иные таблицы в зависимости от того, делится ли user_id без остатка на 2, 3 или 5. Если значение user_id без остатка делится на 2, то строка вставляется в users2, если на 3, то в users3 , если на 5, то в users5 , и если не делится ни на одно из этих чисел, то - в таблицу users0 .
Как видим, команда INSERT вставила больше строк, чем возвращает подзапрос. Это потому, что некоторые user_id одновременно делятся более чем на одно из чисел 2, 3 и 5. Найдем строки, вставленные более чем в одну таблицу:
Теперь выполним ту же команду с незначительным изменением, а именно, поставим после INSERT ключевое слово FIRST :
Каждая из возвращенных подзапросом строк попала ровно в одну из таблиц! Посмотрим, как распределились строки по таблицам:
Если поменять порядок условий WHEN в команде INSERT , то результат выполнения команды будет другим. Например,
В заключение, еще один эксперимент. Попробую вставить строки более чем в одну таблицу при выполнении / невыполнении условия WHEN :
Работает! Посмотрим, как распределились cтроки по таблицам:
Для многотабличной условной команды INSERT , как и для ее безусловного варианта, таблицы в частях INTO могут быть совершенно разными по структуре. Все, сказанное в части I об использовании сиквенсов с многотабличной командой INSERT , справедливо также для условного варианта этой команды.
Читайте также: