Insérer (SQL) - Insert (SQL)

Une instruction SQL INSERT ajoute un ou plusieurs enregistrements à n'importe quelle table d'une base de données relationnelle .

Forme basique

Les instructions d'insertion ont la forme suivante :

INSERT INTO tableau ( colonne1 [, colonne2 , colonne3 ...]) VALUES ( valeur1 [, valeur2 , valeur3 ...])

Le nombre de colonnes et de valeurs doit être le même. Si aucune colonne n'est spécifiée, la valeur par défaut de la colonne est utilisée. Les valeurs spécifiées (ou implicite) par l' INSERT déclaration doit satisfaire à toutes les contraintes applicables (telles que les clés primaires , VOIR contraintes et NOT NULL contraintes). Si une erreur de syntaxe se produit ou si des contraintes sont violées, la nouvelle ligne n'est pas ajoutée à la table et une erreur est renvoyée à la place.

Exemple:

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

La sténographie peut également être utilisée, en profitant de l'ordre des colonnes lors de la création du tableau. Il n'est pas obligatoire de spécifier toutes les colonnes de la table car toutes les autres colonnes prendront leur valeur par défaut ou resteront nulles :

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

Exemple pour insérer des données dans 2 colonnes dans la table phone_book et ignorer toutes les autres colonnes qui peuvent être après les 2 premières de la table.

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

Formulaires avancés

Inserts multi-rangs

Une fonctionnalité SQL (depuis SQL-92 ) consiste à utiliser des constructeurs de valeurs de ligne pour insérer plusieurs lignes à la fois dans une seule instruction SQL :

INSERT INTO tablename (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 - soit 2008), PostgreSQL (depuis la version 8.2), MySQL , SQLite (depuis la version 3.7.11) et H2 .

Exemple (en supposant que 'name' et 'number' sont les seules colonnes de la table 'phone_book') :

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

qui peut être considéré comme un raccourci pour les deux déclarations

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

Notez que les deux instructions distinctes peuvent avoir une sémantique différente (en particulier en ce qui concerne les déclencheurs d' instructions ) et peuvent ne pas fournir les mêmes performances qu'une seule insertion multiligne.

Pour insérer plusieurs lignes dans MS SQL, vous pouvez utiliser une telle construction :

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

Notez qu'il ne s'agit pas d'une instruction SQL valide selon la norme SQL ( SQL:2003 ) en raison de la clause de sous-sélection incomplète.

Pour faire de même dans Oracle, utilisez la table DUAL , qui se compose toujours d'une seule ligne :

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

Une implémentation conforme aux normes de cette logique montre l'exemple suivant, ou comme indiqué ci-dessus :

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 prend en charge l' instruction INSERT ALL , où plusieurs instructions d'insertion se terminent par un SELECT :

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

Dans Firebird, l' insertion de plusieurs lignes peut être réalisée comme ceci :

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, cependant, limite le nombre de lignes pouvant être insérées de cette manière, car il existe une limite au nombre de contextes pouvant être utilisés dans une seule requête.

Copier des lignes d'autres tables

Une instruction INSERT peut également être utilisée pour récupérer des données d'autres tables, les modifier si nécessaire et les insérer directement dans la table. Tout cela se fait dans une seule instruction SQL qui n'implique aucun traitement intermédiaire dans l'application cliente. Une sous-sélection est utilisée à la place de la clause VALUES . La sous-sélection peut contenir des jointures, des appels de fonction et peut même interroger la même table dans laquelle les données sont insérées. Logiquement, la sélection est évaluée avant le démarrage de l'opération d'insertion réelle. Un exemple est donné ci-dessous.

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

Une variation est nécessaire lorsque certaines des données de la table source sont insérées dans la nouvelle table, mais pas l'ensemble de l'enregistrement. (Ou lorsque les schémas des tables ne sont pas les mêmes.)

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

L' instruction SELECT produit une table (temporaire) et le schéma de cette table temporaire doit correspondre au schéma de la table dans laquelle les données sont insérées.

Les valeurs par défaut

Il est possible d'insérer une nouvelle ligne sans spécifier de données, en utilisant les valeurs par défaut pour toutes les colonnes. Cependant, certaines bases de données rejettent l'instruction si aucune donnée n'est fournie, comme Microsoft SQL Server, et dans ce cas le mot clé DEFAULT peut être utilisé.

INSERT INTO phone_book
VALUES ( DEFAULT )

Parfois, les bases de données prennent également en charge une syntaxe alternative pour cela ; par exemple, MySQL permet d'omettre le mot-clé DEFAULT et T-SQL peut utiliser DEFAULT VALUES au lieu de VALUES(DEFAULT) . Le mot clé DEFAULT peut également être utilisé dans une insertion normale pour remplir explicitement une colonne en utilisant la valeur par défaut de cette colonne :

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

Ce qui se passe lorsqu'une colonne ne spécifie pas de valeur par défaut dépend de la base de données. Par exemple, MySQL et SQLite rempliront avec une valeur vide (sauf en mode strict), tandis que de nombreuses autres bases de données rejetteront l'instruction.

Récupérer la clé

Les concepteurs de bases de données qui utilisent une clé de substitution comme clé primaire pour chaque table se heurteront à des scénarios occasionnels où ils doivent récupérer automatiquement la clé primaire générée par la base de données à partir d'une instruction SQL INSERT pour une utilisation dans d'autres instructions SQL. La plupart des systèmes n'autorisent pas les instructions SQL INSERT à renvoyer des données de ligne. Par conséquent, il devient nécessaire d'implémenter une solution de contournement dans de tels scénarios. Les implémentations courantes incluent :

  • À l'aide d'une procédure stockée spécifique à la base de données qui génère la clé de substitution, effectue l' opération INSERT et renvoie enfin la clé générée. Par exemple, dans Microsoft SQL Server, la clé est récupérée via la fonction spéciale SCOPE_IDENTITY() , tandis que dans SQLite la fonction est nommée last_insert_rowid() .
  • Utilisation d'une instruction SELECT spécifique à la base de données sur une table temporaire contenant les dernières lignes insérées. DB2 implémente cette fonctionnalité de la manière suivante :
    SELECT *
    FROM NEW TABLE (
        INSERT INTO phone_book
        VALUES ( 'Peter Doe','555-2323' )
    ) AS t
    
    • DB2 for z/OS implémente cette fonction de la manière suivante.
      SELECT EMPNO, HIRETYPE, HIREDATE
      FROM FINAL TABLE (
          INSERT INTO EMPSAMP (NAME, SALARY, DEPTNO, LEVEL)
          VALUES('Mary Smith', 35000.00, 11, 'Associate')
      );
      
  • Utilisation d'une instruction SELECT après l' instruction INSERT avec une fonction spécifique à la base de données qui renvoie la clé primaire générée pour la dernière ligne insérée. Par exemple, LAST_INSERT_ID() pour MySQL .
  • Utilisation d'une combinaison unique d'éléments du SQL INSERT d'origine dans une instruction SELECT suivante .
  • Utilisation d'un GUID dans l'instruction SQL INSERT et récupération dans une instruction SELECT .
  • Utilisation de la clause OUTPUT dans l'instruction SQL INSERT pour MS-SQL Server 2005 et MS-SQL Server 2008.
  • Utilisation d'une instruction INSERT avec clause RETURNING pour Oracle .
    INSERT INTO phone_book VALUES ( 'Peter Doe','555-2323' )
    RETURNING phone_book_id INTO v_pb_id
    
  • Utilisation d'une instruction INSERT avec clause RETURNING pour PostgreSQL (depuis 8.2). La liste renvoyée est identique au résultat d'un INSERT .
    • Firebird a la même syntaxe dans les instructions Data Modification Language (DSQL) ; l'instruction peut ajouter au plus une ligne. Dans les procédures stockées, les déclencheurs et les blocs d'exécution (PSQL), la syntaxe Oracle susmentionnée est utilisée.
      INSERT INTO phone_book VALUES ( 'Peter Doe','555-2323' )
      RETURNING phone_book_id
      
  • L'utilisation de la fonction IDENTITY() dans H2 renvoie la dernière identité insérée.
    SELECT IDENTITY();
    

Déclencheurs

Si des déclencheurs sont définis sur la table sur laquelle l' instruction INSERT opère, ces déclencheurs sont évalués dans le contexte de l'opération. Les triggers BEFORE INSERT permettent de modifier les valeurs qui doivent être insérées dans la table. Les déclencheurs AFTER INSERT ne peuvent plus modifier les données, mais peuvent être utilisés pour lancer des actions sur d'autres tables, par exemple, pour implémenter un mécanisme d'audit.

Les références

Liens externes