SQL Server Максимальное количество строк, которые можно вставить в одну вставку

Я хочу сделать пакетную вставку, похожую на этот вопрос

Как сделать пакетную вставку в MySQL

  1. Каково ограничение SQL Server на количество строк, которые можно вставить в один оператор вставки?

  2. Что происходит, когда, например, первое значение вставлено, а второе вызывает нарушение первичного ключа. Все INSERT заявления откатились?

INSERT INTO tbl_name (a,b) 
VALUES (1, 2), (1, 3));
Источник

7 ответов

Решение

Максимальное количество строк, которое вы можете вставить в один оператор, составляет 1000 при использовании INSERT INTO ... VALUES... т.е.

INSERT INTO TableName( Colum1)
VALUES (1),
       (2),
       (3),...... upto 1000 rows.

Но если вы используете оператор SELECT для вставки строк в таблицу, для этого нет ограничений, что-то вроде...

INSERT INTO TableName (ColName)
Select Col FROM AnotherTable

Теперь перейдем ко второму вопросу. Что происходит, когда возникает ошибка во время вставки.

Хорошо, если вы вставляете строки, используя многозначную конструкцию

INSERT INTO TableName( Colum1)
VALUES (1),
       (2),
       (3)

В приведенном выше сценарии, если какая-либо вставка строки вызывает ошибку, весь оператор будет откатан, и ни одна из строк не будет вставлена.

Но если вы вставляете строки с отдельным оператором для каждой строки, т.е....

INSERT INTO TableName( Colum1) VALUES (1)
INSERT INTO TableName( Colum1) VALUES (2)
INSERT INTO TableName( Colum1) VALUES (3)

В приведенном выше случае каждая вставка строки представляет собой отдельную инструкцию, и если какая-либо вставка строки вызвала ошибку, только тот конкретный оператор вставки будет откатан, остальные будут успешно вставлены.

Источник

Хотя максимальное значение составляет 1000, было продемонстрировано, что производительность начинает снижаться при значительно меньших значениях. Евгений Филиппов написал отличную статью, исследующую эту тему:

https://www.red-gate.com/simple-talk/sql/performance/comparing-multiple-rows-insert-vs-single-row-insert-with-three-data-load-methods/

Подводя итог, автор сделал несколько очень хорошо продуманных экспериментов и нашел сладкое пятно около 25 лет. YMMV.

Источник

Фактически вы можете передать неограниченное количество записей с помощью подзапроса.

;WITH NewData AS (SELECT * FROM ( VALUES  (1, 'A'),(2,'B'),(3,'C')) x (Id, SomeName))
INSERT INTO TableName (Column1, Column2) SELECT Id, SomeName FROM NewData
Источник

Решение голландца классное, но его можно упростить. Получается, что вам не нужен CTE. Я попробовал, и он работает без CTE, вот так:

      INSERT INTO TableName (Column1, Column2) SELECT Id, SomeName
  FROM  ( VALUES  (1, 'A'),(2,'B'),(3,'C')) x (Id, SomeName)
Источник

Вот как я добавляю более 1000 значений во временную таблицу.

      CREATE TABLE #TempTable(ID int)

INSERT INTO #TempTable (ID)
SELECT * from  (VALUES (45764),(45763),(45762),(45761),(45760),(45759),(45758),(45757),(45756)....)AS temp (column1)
Источник

Существует короткий обходной путь, позволяющий избежать ограничения количества строк и по-прежнему рассматривать его как один оператор (все идет или, если есть одна ошибка, все откатывается)

INSERT INTO tbl_name (a,b) 
SELECT 1,2 UNION ALL
SELECT 1,3 UNION ALL
SELECT 1,4 .......
Источник

Ты можешь попробовать это

with tempDataTable AS (SELECT *From (VALUES
(18001,79626,'1992-12-11','1993-12-11') -- this is data u want to insert
)x(empNO,sal,frmDate,toDate)) -- tempDataColoumns
INSERT INTO salaries(emp_no,salary,from_date,to_date) SELECT empNO,sal,frmDate,toDate from newData

Удалите '-' во время запроса

Источник
Другие вопросы по тегам