Insérer (SQL)

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

Les instructions d'insertion ont la forme suivante :

INSÉRER DANS la table ( colonne1 [, colonne2 , colonne3 ... ]) VALEURS ( valeur1 [, valeur2 , valeur3 ... ])

Le nombre de colonnes et de valeurs doit être identique. 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 implicites) par l' instruction INSERT doivent satisfaire toutes les contraintes applicables (telles que les contraintes de clés primaires , les contraintes CHECK et les contraintes NOT NULL ). 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:

INSÉRER DANS phone_book ( nom , numéro ) VALEURS ( 'John Doe' , '555-1212' );

Il est également possible d'utiliser un raccourci, en profitant de l'ordre des colonnes lors de la création du tableau. Il n'est pas nécessaire de spécifier toutes les colonnes du tableau puisque toutes les autres colonnes prendront leur valeur par défaut ou resteront nulles :

INSÉRER DANS LA TABLE VALEURS ( valeur1 , [ valeur2 , ... ])

Exemple d'insertion de données dans 2 colonnes de la table phone_book et d'ignorance de toutes les autres colonnes qui peuvent se trouver après les 2 premières de la table.

INSÉRER DANS LES VALEURS DE L'annuaire téléphonique ( 'John Doe' , '555-1212' );

Une fonctionnalité SQL (depuis SQL-92 ) est l'utilisation de constructeurs de valeurs de ligne pour insérer plusieurs lignes à la fois dans une seule instruction SQL :

INSÉRER DANS nom_table ( colonne - a , [ colonne - b , ...]) VALEURS ( 'valeur-1a' , [ 'valeur-1b' , ...]), ( 'valeur-2a' , [ 'valeur-2b' , ...]), ...

Cette fonctionnalité est prise en charge par IBM 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 « nom » et « numéro » sont les seules colonnes de la table « phone_book ») :

INSÉRER DANS LES VALEURS de phone_book ( 'John Doe' , '555-1212' ), ( 'Peter Doe' , '555-2323' );

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

INSÉRER DANS LES VALEURS DE L'annuaire téléphonique ( 'John Doe' , '555-1212' ); INSÉRER DANS LES VALEURS DE L'annuaire téléphonique ( '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 multi-lignes.

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

INSÉRER DANS phone_book SÉLECTIONNER 'John Doe' , '555-1212' UNIONER TOUT SÉLECTIONNER '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 la même chose dans Oracle, utilisez la table DUAL , qui se compose toujours d'une seule ligne :

INSÉRER DANS LE RÉPERTOIRE SÉLECTIONNEZ 'John Doe' , '555-1212' DANS DUAL UNION TOUS SÉLECTIONNEZ 'Peter Doe' , '555-2323' DANS DUAL

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

INSÉRER DANS phone_book SÉLECTIONNER 'John Doe' , '555-1212' DE LATERAL ( VALEURS ( 1 ) ) AS t ( c ) UNION TOUT SÉLECTIONNER 'Peter Doe' , '555-2323' DE LATERAL ( VALEURS ( 1 ) ) AS t ( c )

Oracle PL/SQL prend en charge l' instruction INSERT ALL , où plusieurs instructions d'insertion sont terminées par un SELECT :

INSÉRER TOUTES LES VALEURS DANS phone_book ( 'John Doe' , '555-1212' ) DANS LES VALEURS phone_book ( 'Peter Doe' , '555-2323' ) SÉLECTIONNER * DANS DUAL ;

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

INSÉRER DANS phone_book ( nom , numéro ) SÉLECTIONNEZ 'John Doe' , '555-1212' DANS RDB$DATABASE UNION TOUT SÉLECTIONNEZ 'Peter Doe' , '555-2323' DANS RDB$DATABASE ;

Firebird limite cependant 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.

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 est effectué 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 proprement dite. Un exemple est donné ci-dessous.

INSÉRER DANS phone_book2 SÉLECTIONNER * DANS phone_book OÙ nom DANS ( 'John Doe' , 'Peter Doe' )

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

INSÉRER DANS phone_book2 ( nom , numéro ) SÉLECTIONNER nom , numéro DANS phone_book OÙ nom DANS ( '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.

Il est possible d'insérer une nouvelle ligne sans spécifier de données, en utilisant des 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é.

INSÉRER DANS LES VALEURS DU RÉPERTOIRE ( PAR DÉFAUT )

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 :

INSÉRER DANS LES VALEURS DE L'annuaire téléphonique ( PAR DÉFAUT , '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.

Les concepteurs de bases de données qui utilisent une clé de substitution comme clé primaire pour chaque table se retrouveront parfois confrontés à un scénario dans lequel 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 l'utiliser 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 de mettre en œuvre une solution de contournement dans de tels scénarios. Les implémentations courantes incluent :

• Utilisation d'une procédure stockée spécifique à la base de données qui génère la clé de substitution, exécute l' opération INSERT et renvoie finalement 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 la ou les dernières lignes insérées. Db2 implémente cette fonctionnalité de la manière suivante :

  SÉLECTIONNEZ * DANS LA NOUVELLE TABLE ( INSÉRER DANS LES VALEURS DE L'annuaire téléphonique ( 'Peter Doe' , '555-2323' ) ) AS t
  

  • Db2 pour z/OS implémente cette fonctionnalité de la manière suivante.

    SÉLECTIONNEZ EMPNO , HIRETYPE , HIREDATE DANS LA TABLE FINALE ( INSÉRER DANS EMPSAMP ( NOM , SALAIRE , DEPTNO , NIVEAU ) VALEURS ( '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 ligne insérée la plus récemment. Par exemple, LAST_INSERT_ID() pour MySQL .
• Utilisation d’une combinaison unique d’éléments de l’instruction SQL INSERT d’origine dans une instruction SELECT ultérieure .
• 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 .

  INSÉRER DANS LES VALEURS phone_book ( 'Peter Doe' , '555-2323' ) RENVOYER phone_book_id DANS 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'une instruction INSERT .
  • Firebird a la même syntaxe dans les instructions DSQL (Data Modification Language) ; 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.

    INSÉRER DANS LES VALEURS phone_book ( 'Peter Doe' , '555-2323' ) RENVOYER phone_book_id
    

• L'utilisation de la fonction IDENTITY() dans H2 renvoie la dernière identité insérée.

  SÉLECTIONNER L'IDENTITÉ ();
  

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 déclencheurs 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 initier des actions sur d'autres tables, par exemple pour implémenter un mécanisme d'audit.