Jeśli używasz SQL i komendy INSERT aby wstawiać, do bazy danych, duże ilości danych to pokażę Ci jak robić to wydajniej. Dostępne w wielu motorach baz danych. Na przykład PostgreSQL, SQL Server, MySQL, MariaDB i Oracle.

Klasyczne podejście

Jeśli, więc do tej pory pracowicie konstruujesz pętle budując sekwencje komend SQL wyglądające jak ta:

INSERT INTO tabela (kol1, kol2) VALUES (11,12);
INSERT INTO tabela (kol1, kol2) VALUES (21,22);
INSERT INTO tabela (kol1, kol2) VALUES (31,32);

i masz dość powtarzania 'INSERT INTO … VALUES’ za każdym razem. To zmień podejście na wydajniejsze. Przy okazji zaoszczędzisz czas potrzebny na napisanie wielu powtarzalnych klauzul.

Zbiorowy INSERT w SQL

Motory baz danych operują na zbiorach danych. Są do tego zaprojektowane. Nie jest, więc zaskoczeniem, że masowe operacje są wydajniejsze niż wiele pojedynczych. W ten trend wpisuje się wielorekordowy INSERT.

Pojawił się w standardzie SQL-92 jako 'row value constructors’. Znany również jako 'table value constructor’.

Potrafi znacząco skrócić czas potrzebny na załadowanie danych. Zużywa mniej zasobów motoru bazy danych. Wykonuje także mniej komunikacji sieciowej klient<->instancja bazy danych.

Szczególnie istotne jeśli płacisz za transfer sieciowy i CPU hosta z bazą danych. Na przykład dostawcy chmury.

Wiele komend INSERT w jednej transakcji

UWAGA! Zamykanie wielu pojedynczych komend INSERT w jednej transakcji w taki sposób:

BEGIN TRANSACTION;
INSERT INTO tabela (kol1, kol2) VALUES (11,12);
INSERT INTO tabela (kol1, kol2) VALUES (21,22);
COMMIT;

NIE jest równoznaczne z wielorekordowym INSERT. W dalszym ciągu każda z komend jest osobno parsowana, miejsce na dane osobno alokowane, a indeksy osobno modyfikowane.

Jak wstawić wiele rekordów w PostgreSQL, SQL Server, MySQL, MariaDB i Oracle od wersji 23ai?

W PostgreSQL, SQL Server, MySQL, MariaDB i Oracle 23ai wstawiasz wiele rekordów przy pomocy jednej komendy INSERT w taki sposób:

INSERT INTO tabela (kol1, kol2) VALUES -- pojedyncza komenda INSERT
(1,1)   -- rekord 1
,(2,2)  -- rekord 2
,(3,3); -- rekord 3

SQL Server ma limit 1000 rekordów, które jest w stanie wstawić jedną komendą SQL.

W klauzuli VALUES musisz zawsze podać tyle wartości ile kolumn specyfikujesz w klauzuli INTO. W przypadku gdy wstawiasz rekordy bez wszystkich wartości, możesz bazować na, zdefiniowanych dla kolumny, wartościach domyślnych. Lub Wstawić NULL. Robisz to tak:

INSERT INTO tabela (kol1,kol2) VALUES
(DEFAULT,2)
,(11,22)
,(21,NULL);

Powyższa składnia nie pozwala na użycie podzapytań zwracających więcej niż pojedynczą wartość. Można to obejść w taki sposób:

INSERT INTO tabela1 (kol1, kol2) VALUES
(11,12)
,(21,22)
,((SELECT kol1 FROM tabela2 WHERE id=321), (SELECT kol2 FROM tabela2 WHERE id=321));

Jak wstawić wiele rekordów w Oracle pre 23ai?

Oracle ma inną składnię niż zaprezentowana powyżej. Wygląda tak:

INSERT ALL
  INTO tabela (kol1, kol2) VALUES (1,1)
  INTO tabela (kol1, kol2) VALUES (2,2)
  INTO tabela (kol1, kol2) VALUES (3,3)
SELECT * FROM dual;

Sposób Oracle ma taką zaletę, że jedną komendą INSERT, możesz wstawiać wiele rekordów do wielu tabel jednocześnie. Na przykład tak:

INSERT ALL
  INTO tabela1 (kol11, kol12) VALUES (11,12)
  INTO tabela2 (kol21, kol22) VALUES (21,22)
  INTO tabela3 (kol31, kol32) VALUES (31,32)
SELECT * FROM dual;
COMMIT;

Jest to wielotabelowy INSERT. Choć funkcjonalnie realizuje te same cele – umożliwia wstawienie wielu rekordów jedną komendą.

Przetestuj to na swojej bazie danych tworząc 3 tabele:

CREATE TABLE tabela1(
  kol11 number(2)
  ,kol12 number(2)
);
CREATE TABLE tabela2(
  kol21 number(2)
  ,kol22 number(2)
);
CREATE TABLE tabela3(
  kol31 number(2)
  ,kol32 number(2)
);

A następnie wykonaj poprzedni INSERT ALL nie zapominając o COMMIT. I sprawdź czy dane zostały wstawione:

SELECT * FROM tabela1
UNION ALL
SELECT * FROM tabela2
UNION ALL
SELECT * FROM tabela3;

Jak wszystko poszło smukle to zobaczysz taki wynik w SQL Developerze:

Umożliwia również wstawianie warunkowe. Ale to już temat na osobny wpis.

Składnia Oracle ma swoje limity. Nie są one jednak sprecyzowane jedną liczbą. Jest to iloczyn liczby kolumn tabeli docelowej i liczby klauzul INTO w komendzie INSERT ALL. Wg terminologii Oracle jest to multi-table INSERT.

Wielorekordowy INSERT to wszystko albo nic

Wielorekordowy INSERT reprezentuje podejście wszystko albo nic. Czyli albo powiedzie się wstawienie wszystkich rekordów wymienionych w komendzie INSERT, albo żaden z rekordów nie zostanie wstawiony. Warto mieć to na uwadze.

Gdy zależy Ci na innym podejściu – na przykład na rozwiązywaniu konfliktów lub zamianie INSERT na UPDATE – rozważ komendę MERGE. Będę o tym pisał w następnym wpisie.

Artykuł o podstawach komendy INSERT języka SQL: Podstawy INSERT w SQL. Dotyczy Oracle, PostgreSQL, MySQL i SQL Server.

Artykuł o tym jak najszybciej wstawić do bazy danych jak największą ilość rekordów zużywając jak najmniej zasobów instancji: Jak efektywnie wstawić wiele rekordów do tabeli wykorzystując SQL?. Wszystko przy pomocy SQL i języków proceduralnych na przykładzie Oracle i PostgreSQL.

Artykuł o tym jak komendą INSERT języka SQL wstawić do bazy danych dane typu znakowego, numerycznego i datoczasowego: Jak dodać rekord do tabeli? Komenda INSERT języka SQL. Na przykładzie bazy danych Oracle.

Prowadzę szkolenia i kursy z podstaw SQL. Sprawdź ofertę moich kursów SQL.