Wstaw (SQL) - Insert (SQL)

SQL INSERT instrukcja dodaje jeden lub więcej rekordów do każdej pojedynczej tabeli w relacyjnej bazie danych .

Podstawowa forma

Wstaw oświadczenia mają następującą postać: 

INSERT INTO tabela ( kolumna1 [, kolumna2 , kolumna3 ... ]) VALUES ( wartość1 [ , wartość2 , wartość3 ... ])

Liczba kolumn i wartości muszą być takie same. Jeśli kolumna nie jest określona, ​​używana jest wartość domyślna dla kolumny. Podane wartości (lub domniemane) przez INSERT oświadczenie musi spełniać wszelkie obowiązujące ograniczenia (takie jak klucze podstawowego , CENY ograniczeń i NOT NULL ograniczeń). W przypadku wystąpienia błędu składni lub naruszenia jakichkolwiek ograniczeń nowy wiersz nie jest dodawany do tabeli, a zamiast tego zwracany jest błąd.

Przykład: 

INSERT INTO phone_book (name, number) VALUES ('John Doe', '555-1212');

Można również użyć skrótów, wykorzystując kolejność kolumn w momencie tworzenia tabeli. Nie jest wymagane określenie wszystkich kolumn w tabeli, ponieważ wszystkie inne kolumny przyjmą wartość domyślną lub pozostaną null : 

INSERT INTO table VALUES ( wartość1 , [ wartość2 , ... ])

Przykład wstawiania danych do 2 kolumn w tabeli phone_book i ignorowania wszelkich innych kolumn, które mogą znajdować się po pierwszych 2 w tabeli. 

INSERT INTO phone_book VALUES ('John Doe', '555-1212');

Zaawansowane formularze

Wkładki wielorzędowe

Cechą SQL (od SQL-92 ) jest użycie konstruktorów wartości wierszy do wstawiania wielu wierszy naraz w jednej instrukcji SQL: 

INSERT INTO tablename (column-a, [column-b, ...])
VALUES ('value-1a', ['value-1b', ...]),
       ('value-2a', ['value-2b', ...]),
       ...

Ta funkcja jest obsługiwana przez DB2 , SQL Server (od wersji 10.0 - tj. 2008), PostgreSQL (od wersji 8.2), MySQL , SQLite (od wersji 3.7.11) i H2 .

Przykład (zakładając, że „nazwisko” i „numer” to jedyne kolumny w tabeli „phone_book”): 

INSERT INTO phone_book VALUES ('John Doe', '555-1212'), ('Peter Doe', '555-2323');

co może być postrzegane jako skrót dla tych dwóch stwierdzeń 

INSERT INTO phone_book VALUES ('John Doe', '555-1212');
INSERT INTO phone_book VALUES ('Peter Doe', '555-2323');

Należy zauważyć, że dwie oddzielne instrukcje mogą mieć różną semantykę (zwłaszcza w odniesieniu do wyzwalaczy instrukcji ) i mogą nie zapewniać takiej samej wydajności, jak pojedyncze wstawianie wielu wierszy.

Aby wstawić wiele wierszy w MS SQL można użyć takiej konstrukcji: 

INSERT INTO phone_book
SELECT 'John Doe', '555-1212'
UNION ALL
SELECT 'Peter Doe', '555-2323';

Należy zauważyć, że nie jest to poprawna instrukcja SQL zgodnie ze standardem SQL ( SQL:2003 ) z powodu niekompletnej klauzuli podselekcji.

Aby zrobić to samo w Oracle, użyj tabeli DUAL , która zawsze składa się tylko z jednego wiersza: 

INSERT INTO phone_book
SELECT 'John Doe', '555-1212' FROM DUAL
UNION ALL
SELECT 'Peter Doe','555-2323' FROM DUAL

Implementacja tej logiki zgodna ze standardami przedstawia następujący przykład lub jak pokazano powyżej: 

INSERT INTO phone_book
SELECT 'John Doe', '555-1212' FROM LATERAL ( VALUES (1) ) AS t(c)
UNION ALL
SELECT 'Peter Doe','555-2323' FROM LATERAL ( VALUES (1) ) AS t(c)

Oracle PL/SQL obsługuje instrukcję INSERT ALL , w której wiele instrukcji INSERT kończy się poleceniem SELECT : 

INSERT ALL
INTO phone_book VALUES ('John Doe', '555-1212')
INTO phone_book VALUES ('Peter Doe', '555-2323')
SELECT * FROM DUAL;

W Firebird wstawianie wielu wierszy można wykonać w następujący sposób: 

INSERT INTO phone_book (name, number)
SELECT 'John Doe', '555-1212' FROM RDB$DATABASE
UNION ALL
SELECT 'Peter Doe', '555-2323' FROM RDB$DATABASE;

Firebird ogranicza jednak liczbę wierszy, które można w ten sposób wstawić, ponieważ istnieje limit liczby kontekstów, które można wykorzystać w pojedynczym zapytaniu.

Kopiowanie wierszy z innych tabel

INSERT oświadczenie może być również używany do pobierania danych z innych tabel, modyfikować je w razie potrzeby i wstawić go bezpośrednio do stołu. Wszystko to odbywa się za pomocą pojedynczej instrukcji SQL, która nie wymaga żadnego przetwarzania pośredniego w aplikacji klienckiej. Podselekcja jest używana zamiast klauzuli VALUES . Podselekcja może zawierać sprzężenia, wywołania funkcji, a nawet może wysyłać zapytania do tej samej tabeli, do której wstawiane są dane. Logicznie, wybór jest oceniany przed rozpoczęciem właściwej operacji wstawiania. Przykład podano poniżej. 

INSERT INTO phone_book2
SELECT *
FROM   phone_book
WHERE  name IN ('John Doe', 'Peter Doe')

Zmiana jest potrzebna, gdy część danych z tabeli źródłowej jest wstawiana do nowej tabeli, ale nie cały rekord. (Lub gdy schematy tabel nie są takie same). 

INSERT INTO phone_book2 (name, number)
SELECT name, number
FROM   phone_book
WHERE  name IN ('John Doe', 'Peter Doe')

Instrukcja SELECT tworzy tabelę (tymczasową), a schemat tej tabeli tymczasowej musi być zgodny ze schematem tabeli, do której wstawiane są dane.

Wartości domyślne

Możliwe jest wstawienie nowego wiersza bez podawania jakichkolwiek danych, używając wartości domyślnych dla wszystkich kolumn. Jednak niektóre bazy danych odrzucają instrukcję, jeśli nie podano żadnych danych, np. Microsoft SQL Server iw tym przypadku można użyć słowa kluczowego DEFAULT . 

INSERT INTO phone_book
VALUES ( DEFAULT )

Czasami bazy danych obsługują również alternatywną składnię; na przykład MySQL pozwala na pominięcie słowa kluczowego DEFAULT , a T-SQL może używać DEFAULT VALUES zamiast VALUES(DEFAULT) . DEFAULT kluczowe mogą być również wykorzystywane w normalnym wkładania jawnie wypełnić kolumnę przy użyciu wartości domyślnych, które kolumny: 

INSERT INTO phone_book VALUES ( DEFAULT, '555-1212' )

Co się dzieje, gdy kolumna nie określa wartości domyślnej, zależy od bazy danych. Na przykład MySQL i SQLite wypełnią pustą wartość (z wyjątkiem trybu ścisłego), podczas gdy wiele innych baz danych odrzuci instrukcję.

Pobieranie klucza

Projektanci baz danych, którzy używają klucza zastępczego jako klucza podstawowego dla każdej tabeli, sporadycznie napotkają scenariusz, w którym będą musieli automatycznie pobrać klucz podstawowy wygenerowany przez bazę danych z instrukcji SQL INSERT do użycia w innych instrukcjach SQL. Większość systemów nie zezwala na zwracanie danych w wierszach instrukcji SQL INSERT . Dlatego konieczne staje się wdrożenie obejścia w takich scenariuszach. Typowe wdrożenia obejmują:

  • Korzystanie z procedury składowanej specyficznej dla bazy danych , która generuje klucz zastępczy, wykonuje operację INSERT i na koniec zwraca wygenerowany klucz. Na przykład w Microsoft SQL Server klucz jest pobierany za pomocą funkcji specjalnej SCOPE_IDENTITY() , podczas gdy w SQLite funkcja ta nosi nazwę last_insert_rowid() .
  • Użycie instrukcji SELECT specyficznej dla bazy danych w tabeli tymczasowej zawierającej ostatnio wstawione wiersze. DB2 implementuje tę funkcję w następujący sposób: 
    SELECT *
FROM NEW TABLE (
    INSERT INTO phone_book
    VALUES ( 'Peter Doe','555-2323' )
) AS t
    • DB2 for z/OS implementuje tę funkcję w następujący sposób. 
      SELECT EMPNO, HIRETYPE, HIREDATE
FROM FINAL TABLE (
    INSERT INTO EMPSAMP (NAME, SALARY, DEPTNO, LEVEL)
    VALUES('Mary Smith', 35000.00, 11, 'Associate')
);
  • Użycie instrukcji SELECT po instrukcji INSERT z funkcją specyficzną dla bazy danych, która zwraca wygenerowany klucz podstawowy dla ostatnio wstawionego wiersza. Na przykład LAST_INSERT_ID() dla MySQL .
  • Użycie unikalnej kombinacji elementów z oryginalnej instrukcji SQL INSERT w kolejnej instrukcji SELECT .
  • Użycie identyfikatora GUID w instrukcji SQL INSERT i pobranie go w instrukcji SELECT .
  • Korzystanie z klauzuli OUTPUT w instrukcji SQL INSERT dla MS-SQL Server 2005 i MS-SQL Server 2008.
  • Korzystanie z instrukcji INSERT z klauzulą RETURNING dla Oracle . 
    INSERT INTO phone_book VALUES ( 'Peter Doe','555-2323' )
RETURNING phone_book_id INTO v_pb_id
  • Korzystanie z instrukcji INSERT z klauzulą RETURNING dla PostgreSQL (od wersji 8.2). Zwrócona lista jest identyczna z wynikiem INSERT .
    • Firebird ma taką samą składnię w instrukcjach języka modyfikacji danych (DSQL); oświadczenie może dodać co najwyżej jeden wiersz. W procedurach składowanych, wyzwalaczach i blokach wykonawczych (PSQL) stosowana jest wspomniana składnia Oracle. 
      INSERT INTO phone_book VALUES ( 'Peter Doe','555-2323' )
RETURNING phone_book_id
  • Użycie funkcji IDENTITY() w H2 zwraca ostatnią wstawioną tożsamość. 
    SELECT IDENTITY();

Wyzwalacze

Jeśli wyzwalacze są zdefiniowane w tabeli, na której działa instrukcja INSERT , wyzwalacze te są oceniane w kontekście operacji. Wyzwalacze BEFORE INSERT umożliwiają modyfikację wartości, które mają być wstawione do tabeli. Wyzwalacze AFTER INSERT nie mogą już modyfikować danych, ale mogą być używane do inicjowania akcji na innych tabelach, na przykład do implementacji mechanizmu audytu.

Bibliografia

Zewnętrzne linki