Insert all oracle сколько строк можно вставить
Обновлено: 07.07.2024
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 с локальной процедурой, подобной этой:
Понятно, что DELETE удалит 0 строк, а UPDATE обновит 0 строк, если ни одна из строк таблицы не удовлетворит условию WHERE в предложении DELETE или UPDATE . Но может ли INSERT вставить 0 строк?
Представьте ситуацию: INSERT в цикле срабатывает 100 раз, а в таблицу добавлено 60 строк. Возможно ли это?
Оказывается, возможно. INSERT может вставить 0 строк, если это INSERT с подзапросом SELECT , который возвращает 0 строк.
Это была разминка. Теперь поближе познакомимся с возможностями команды INSERT , позволяющими добавлять строки в несколько таблиц за один раз. Такие многотабличные предложения INSERT используют подзапрос SELECT для получения данных для вставки.
СУБД Oracle, начиная с версии 9, поддерживает два типа многотабличных предложений INSERT : безусловное и условное. В этой статье я экспериментирую с безусловным многотабличным предложеним INSERT .
Безусловное многотабличное предложение INSERT выполняет каждую из частей INTO для каждой строки, возвращенной подзапросом SELECT . Синтаксис такой:
Ключевое слово ALL после INSERT обязательно в безусловном многотабличном предложении.
Для дальнейшего экспериментирования создаю еще одну таблицу со структурой, эквивалентной структуре системного вью all_users :
А теперь одной командой копирую 5 строк из all_users в обе таблицы, users0 и users1 :
И проверяю результат:
В части VALUES многотабличной команды INSERT можно использовать выражения из списка SELECT подзапроса, и вообще все допустимые выражения SQL:
Для наглядности выведу строки, относящиеся к одному и тому же пользователю, рядом:
Если многотабличный INSERT по какой-либо причине завершится ошибкой, то отменяются все изменения, сделанные данной командой, и ни в одну из таблиц строки не будут добавлены. Продемонстрирую это, добавляя пробелы к username до тех пор, пока для очередной вставляемой строки получившееся значение не поместится в столбец username таблицы users0 :
Еще один момент, заслуживающий упоминания, это использование сиквенсов в многотабличной команде INSERT . Oracle не позволяет использовать NEXTVAL в подзапросе:
Однако, можно использовать сиквенс в части VALUE многотабличной команды INSERT :
Сюрприз! Несмотря на то, что выражение s0.NEXTVAL использовано дважды, в списках VALUES для таблиц users0 и users1 , оно было вычислено только один раз, и полученное значение 1 было использовано при вставке в обе таблицы! Каковы бы ни были причины такого поведения, многотабличная команда INSERT ведет себя именно так.
А сколько раз выполнится NEXTVAL , если подзапрос вернет две (или больше) строки?
Итак, вычисление выражения s0.NEXTVAL происходит один раз для каждой строки, возвращаемой подзапросом.
Во всех приведенных выше примерах многотабличная команда INSERT вставляет строки в таблицы с одинаковыми столбцами. Но таблицы в частях INTO вовсе не обязаны иметь одинаковую структуру. Продемонстрирую это:
Завершу знакомство с многотабличной безусловной командой INSERT забавным экспериментом и удалением более не нужных объектов:
Во второй части - исследование условной команды INSERT для нескольких таблиц.
То, как multitable insert работает с сиквенсами, позволяет одной командой вставить строки в родительскую и дочернюю таблицы:
Я ищу хороший способ выполнения многострочных вставок в базу данных Oracle 9. Следующее работает в MySQL, но, похоже, не поддерживается в Oracle.
Это работает в Oracle:
Здесь нужно помнить - использовать это from dual утверждение.
Будучи разборчивым, но форматирование имеет больше смысла, если вы поместите «union all» в конце каждой строки выбора (кроме последней). Одним из недостатков этого мы не можем использовать , sequnce.nextval как это запрещено в union о select . Вместо этого мы можем пойти с INSERT ALL . @Jamie: форматирование Espo немного умнее в том смысле, что вам не нужно беспокоиться о том, находитесь ли вы на последней строке или нет, при добавлении новых строк. Следовательно, когда у вас есть 2 первых выбора, вы можете легко скопировать / вставить последнюю строку (или среднюю), ориентируясь только на значения, которые вы должны изменить. Это обычная уловка для множества других случаев на любых языках (запятая, логические операторы, плюс . ). Это просто привычка, многие прежние практики были пересмотрены, чтобы сосредоточиться на ответственности кода больше, чем на интуитивности.В Oracle, чтобы вставить несколько строк в таблицу t со столбцами col1, col2 и col3, вы можете использовать следующий синтаксис:
INSERT ALL требует SELECT подзапроса. Чтобы обойти это, SELECT 1 FROM DUAL используется, чтобы дать одну строку фиктивных данных. Чем это отличается от нескольких операторов вставки? У вас все еще есть повторение имен столбцов, так что, похоже, не получите много. Около 10-12 множественных операторов INSERT выполняются за 2 секунды на моем ПК, в то время как приведенный выше синтаксис способен вставлять 1000 записей в секунду! Пораженный! Обратите внимание, что я совершаю только в конце. Это работает нормально, однако, если вы вставляете, используя последовательность, скажем user.NEXTVAL, она будет возвращать одно и то же значение для каждой вставки. Вы можете вручную увеличить его во вставке все, а затем обновить последовательность за пределами вставки.Используйте SQL * Loader. Требуется небольшая настройка, но если это не единственное, оно того стоит.
Создать таблицу
Создать CSV
Создать файл управления загрузчиком
Запустите команду SQL * Loader
Подтвердите вставку
SQL * Loader имеет множество опций и может принимать практически любой текстовый файл в качестве входных данных. Вы можете даже вставить данные в свой контрольный файл, если хотите.
В обсуждении о вставке нескольких строк в Oracle были продемонстрированы два подхода:
Может ли кто-нибудь аргументировать предпочтение использования одного над другим?
P.S. Я сам не проводил никаких исследований (даже план объяснений), поэтому любая информация или мнение будут оценены.
С точки зрения производительности эти запросы идентичны.
UNION ALL не повредит производительность, поскольку Oracle оценивает запрос UNION 'ed только тогда, когда он ему нужен, он не кэширует результаты в первую очередь.
SELECT более гибкий в этом смысле, что вы можете более легко получить запрос SELECT , если хотите что-то изменить.
Например, этот запрос:
можно переписать как
Заменив 2 на соответствующее число, вы можете получить любое количество строк, которые вы хотите.
В случае INSERT ALL вам придется дублировать описание таблицы назначения, которое менее читаемо, если вам нужно, скажем, 40 rows.
Я бы предположил, что решение 1 - это немного хак, который работает и, вероятно, менее эффективен, чем разработанная альтернатива Insert ALL.
Вставить все действительно предназначено для того, чтобы вы вставляли много строк в более чем 1 таблицу в результате выбора, например:
Если вы хотите загрузить тысячи строк , а их нет в базе данных уже, я не думаю, что это лучший способ сделать это. Если ваши данные находятся в файле, вы хотите посмотреть внешние таблицы или загрузчик SQL, чтобы эффективно вставлять строки для вас.
Метод INSERT ALL имеет проблему с вставкой большего количества строк в таблицу.
Недавно мне захотелось вставить 1130 строк в таблицу с одним оператором SQL. Когда я попытался сделать это с помощью метода INSERT ALL , я получил следующую ошибку:
ORA-24335 - не может поддерживать более 1000 столбцов
Когда я использовал подход INSERT INTO .. UNION ALL .. , все шло хорошо.
Btw. Я не знал о методе UNION ALL, прежде чем нашел это обсуждение:)
Утверждение, использующее UNION ALL , теоретически представляет собой небольшой недостаток производительности, поскольку он должен объединять результаты всех операторов до того, как вставка может произойти. INSERT ALL не имеет этого недостатка, поскольку конечный результат уже может обрабатываться по очереди.
Но практически оптимизатор внутри Oracle должен сделать разницу незначительной, и это зависит от ваших предпочтений, которые вы выбираете.
По моему мнению, INSERT ALL является лучшим для человека, читаемым для двух, в то время как вариант UNION ALL - это тот, который занимает меньше места, когда такая вставка автоматически генерируется.
Я пробовал некоторые тесты, и более быстрое решение должно быть
Если у вас есть инструкции insert, которые больше 1000, тогда поместите все инструкции insert в файл .sql и откройте это в Toad или SQL Developer, а затем выполните. Все записи будут вставлены.
Вы должны рассмотреть Array-Insert.
- Легкий SQL
- нужно некоторое кодирование на стороне клиента для настройки параметров массива
Это способ минимизировать сетевой трафик, если в пакете должно быть сделано несколько сотен вставок.
Посмотрите другие вопросы по меткам sql oracle insert или Задайте вопрос
Оператор INSERT вставляет новые записи в таблицу. При этом значения столбцов могут представлять собой литеральные константы, либо являться результатом выполнения подзапроса. В первом случае для вставки каждой строки используется отдельный оператор INSERT ; во втором случае будет вставлено столько строк, сколько возвращается подзапросом.
Синтаксис оператора следующий:
Как видно из представленного синтаксиса, список столбцов не является обязательным (об этом говорят квадратные скобки в описании синтаксиса). В том случае, если он отсутствует, список вставляемых значений должен быть полный, то есть обеспечивать значения для всех столбцов таблицы. При этом порядок значений должен соответствовать порядку, заданному оператором CREATE TABLE для таблицы, в которую вставляются строки. Кроме того, эти значения должны относиться к тому же типу данных, что и столбцы, в которые они вносятся. В качестве примера рассмотрим вставку строки в таблицу Product, созданную следующим оператором CREATE TABLE :
Пусть требуется добавить в эту таблицу модель ПК 1157 производителя B. Это можно сделать следующим оператором:
Если задать список столбцов, то можно изменить «естественный» порядок их следования:
Казалось бы, это совершенно излишняя возможность, которая делает конструкцию только более громоздкой. Однако она становится выигрышной, если столбцы имеют значения по умолчанию. Рассмотрим следующую структуру таблицы:
Отметим, что здесь значения всех столбцов имеют значения по умолчанию (первые два — NULL, а последний столбец — type — PC). Теперь мы могли бы написать:
В этом случае отсутствующее значение при вставке строки будет заменено значением по умолчанию — PC. Заметим, что если для столбца в операторе CREATE TABLE не указано значение по умолчанию и не указано ограничение NOT NULL , запрещающее использование NULL в данном столбце таблицы, то подразумевается значение по умолчанию NULL .
Возникает вопрос: а можно ли не указывать список столбцов и, тем не менее, воспользоваться значениями по умолчанию? Ответ положительный. Для этого нужно вместо явного указания значения использовать зарезервированное слово DEFAULT :
Поскольку все столбцы имеют значения по умолчанию, для вставки строки со значениями по умолчанию можно было бы написать:
Однако для этого случая предназначена специальная конструкция DEFAULT VALUES (см. синтаксис оператора), с помощью которой вышеприведенный оператор можно переписать в виде
Заметим, что при вставке строки в таблицу проверяются все ограничения, наложенные на данную таблицу. Это могут быть ограничения первичного ключа или уникального индекса, проверочные ограничения типа CHECK , ограничения ссылочной целостности. В случае нарушения какого-либо ограничения вставка строки будет отклонена. Рассмотрим теперь случай использования подзапроса. Пусть нам требуется вставить в таблицу Product_D все строки из таблицы Product, относящиеся к моделям персональных компьютеров (type = ‘PC’). Поскольку необходимые нам значения уже имеются в некоторой таблице, то формирование вставляемых строк вручную, во-первых, является неэффективным, а, во-вторых, может допускать ошибки ввода. Использование подзапроса решает эти проблемы:
Использование в подзапросе символа «*» является в данном случае оправданным, так как порядок следования столбцов является одинаковым для обеих таблиц. Если бы это было не так, следовало бы применить список столбцов либо в операторе INSERT , либо в подзапросе, либо в обоих местах, который приводил бы в соответствие порядок следования столбцов:
Здесь, также как и ранее, можно указывать не все столбцы, если требуется использовать имеющиеся значения по умолчанию, например:
В данном случае в столбец type таблицы Product_D будет подставлено значение по умолчанию PC для всех вставляемых строк.
Отметим, что при использовании подзапроса, содержащего предикат, будут вставлены только те строки, для которых значение предиката равно TRUE (не UNKNOWN !). Другими словами, если бы столбец type в таблице Product допускал бы NULL -значение, и это значение присутствовало бы в ряде строк, то эти строки не были бы вставлены в таблицу Product_D.
Преодолеть ограничение на вставку одной строки в операторе INSERT при использовании конструктора строки в предложении VALUES позволяет искусственный прием использования подзапроса, формирующего строку с предложением UNION ALL . Так если нам требуется вставить несколько строк при помощи одного оператора INSERT , можно написать:
Использование UNION ALL предпочтительней UNION даже, если гарантировано отсутствие строк-дубликатов, так как в этом случае не будет выполняться проверка для исключения дубликатов.
Следует отметить, что вставка нескольких кортежей с помощью конструктора строк уже реализована в Cистема управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft. Язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. SQL Server 2008. С учетом этой возможности, последний запрос можно переписать в виде:
Заметим, что MySQL допускает еще одну нестандартную синтаксическую конструкцию, выполняющую вставку строки в таблицу в стиле оператора UPDATE:
Рассмотренный в начале параграфа пример с помощью этого оператора можно переписать так:
Читайте также: