Insert (SQL)

INSERT est une commande SQL qui ajoute un ou plusieurs tuples dans une table d'une base de données relationnelle.

Forme basique

La commande INSERT a la syntaxe suivante :

INSERT INTO table (column1 [, column2, column3 ... ]) VALUES (value1 [, value2, value3 ... ])

Le nombre de colonnes doit être identique au nombre de valeurs. Si une colonne n'est pas spécifiée, sa valeur par défaut lui sera affectée. Les valeurs insérées doivent respecter toutes les contraintes tel que les clés étrangères, clés primaires, et les colonnes NOT NULL. Si la commande contient une erreur de syntaxe, ou si une contrainte n'est pas respectée, les valeurs ne sont pas insérées et une erreur est rapportée.

Exemple :

INSERT INTO film_cast (firstname, lastname) VALUES ('Raoul', 'Duke')

Une écriture plus concise existe, sans nommer les colonnes, insérant les valeurs dans le même ordre que les colonnes de la table. Il n'est pas nécessaire de préciser toutes les colonnes, les colonnes restantes prendront leurs valeurs par défaut.

Syntaxe :

INSERT INTO table VALUES (value1, [value2, ... ])

L'exemple précédent peut donc être réecrit, si et seulement si les deux premières colonnes de la table film_cast sont firstname et lastname:

INSERT INTO film_cast VALUES ('Raoul', 'Duke');

Formes avancées

Insertion multiples

Depuis SQL-92 (en) il est possible d'insérer plusieurs tuples en une seule requête :

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

Cette fonctionnalité est supportée par DB2, SQL Server (Depuis la version 10.0), PostgreSQL (depuis la version 8.2), MySQL, SQLite (depuis la version 3.7.11) et H2.

Exemple (considérant que 'firstname' et 'lastname' sont les deux seules colonnes de la table 'film_cast'):

INSERT INTO film_cast VALUES ('Raoul', 'Duke'), ('Dr.', 'Gonzo')

Les insertions multiples sont particulièrement intéressantes en matière de performances. Il est donc conseillé pour une application qui doit insérer beaucoup lignes par seconde d'utiliser des requêtes multiples pour réduire le nombre de requêtes effectuées et la charge du serveur. L'utilisation de requête multiples permet d'insérer plus de tuples qu'une série d'insertions simples pour un temps donné.

Afin d'insérer plusieurs tuples en utilisant MS SQL, la construction suivante est utilisable :

INSERT INTO film_cast
SELECT 'Raoul', 'Duke'
UNION ALL
SELECT 'Dr.', 'Gonzo'

Cependant ce n'est pas une requête SQL valide selon le standard SQL:2023 (en) dû à la sous requête SELECT incomplète.

Il est aussi possible de le faire sous Oracle en utilisant la table DUAL:

INSERT INTO film_cast
SELECT 'Raoul', 'Duke' FROM DUAL
UNION ALL
SELECT 'Dr.','Gonzo' FROM DUAL

Une implémentation respectant les standards SQL:

INSERT INTO film_cast
SELECT 'Raoul', 'Duke' FROM LATERAL ( VALUES (1) ) AS t(c)
UNION ALL
SELECT 'Dr.','Gonzo' FROM LATERAL ( VALUES (1) ) AS t(c)

Oracle PL/SQL supporte la syntaxe "INSERT ALL", dans laquelle les requêtes d'insertions multiples sont terminés par un SELECT:

INSERT ALL
INTO film_cast VALUES ('Raoul', 'Duke')
INTO film_cast VALUES ('Dr.', 'Gonzo')
SELECT * FROM DUAL

Avec la base de données Firebird, l'insertion multiple est possible grâce à la syntaxe :

INSERT INTO film_cast ("firstname", "lastname")
SELECT 'Raoul', 'Duke' FROM RDB$DATABASE
UNION ALL
SELECT 'Dr.', 'Gonzo' FROM RDB$DATABASE

Cependant, Firebird restreint le nombre de tuples qui peuvent être insérés par cette syntaxe dû à la limite du nombre de contextes utilisables en une seule requête.

Copier des tuples depuis une autre table

Une seule requête INSERT peut aussi être utilisée pour récupérer des données depuis une autre table, les modifier si nécessaire, puis les insérer directement dans une table. Dans ce cas, une sous-requête est utilisée à la place de la clause VALUES :

INSERT INTO film_cast2 ( firstname, lastname )
    SELECT firstname, lastname
        FROM film_cast
        WHERE firstname IN ('Raoul', 'Dr.')

Récupération de la clef primaire

Il est souvent nécessaire d'obtenir la cle primaire artificielle qui a été auto-générée par le moteur de base de données lors d'une insertion afin de l'utiliser dans des requêtes suivantes. Il existe, en fonction du moteur utilisé, différentes méthodes pour l'obtenir :

• En utilisant une procédure stockée spécifique au moteur de base de données tel que SCOPE_IDENTITY() en SQL Server, ou last_insert_rowid() en SQLite.

• En utilisant une requête SELECT spécifique au moteur de base de données sur une table temporaire contenant le dernier tuple inséré. DB2 implémente cette fonctionnalité de la manière suivante :

SELECT *
FROM NEW TABLE (
    INSERT INTO film_cast
    VALUES ('Raoul', 'Duke')
) AS t

DB2 pour z/OS implémente cette fonctionnalité de la manière suivante :

SELECT *
FROM FINAL TABLE (
    INSERT INTO film_cast
    VALUES ('Raoul', 'Duke')
)

• En effectuant une requête SELECT après l'INSERT avec une fonction spécifique à la base de données qui renvoie la clef primaire générée pour la dernière insertion effectuée, tel que LAST_INSERT_ID() pour MySQL.
• En utilisant la syntaxe OUTPUT lors de la requête d'insertion, avec MS-SQL Server 2005 et MS-SQL Server 2008.
• En utilisant le mot clé RETURNING avec Oracle :

INSERT INTO film_cast VALUES ('Raoul', 'Duke')
RETURNING film_cast_id INTO v_pb_id

• En utilisant le mot clef RETURNING avec PostgreSQL (depuis la version 8.2). Le résultat est identique au résultat d'un SELECT.

Firebird utilise la même syntaxe, cependant la requête ne pourra insérer qu'un seul tuple.

INSERT INTO film_cast VALUES ('Raoul', 'Duke')
    RETURNING film_cast_id

• En utilisant la fonction IDENTITY() avec H2 :

SELECT IDENTITY()

Déclencheurs (Triggers)

Si des déclencheurs (triggers en anglais) sont définis sur la table dans laquelle la requête insère, ils seront exécutés dans le contexte de la requête. BEFORE INSERT permet la modification des valeurs qui devront être insérées, AFTER INSERT ne peut plus modifier les données mais peut être utilisé pour initier des actions sur d'autres tables.

Articles connexes

• Select (SQL)
• Update (SQL)
• Delete (SQL)