Catégories :

Commandes DML - Général

INSERT

Met à jour une table en y insérant une ou plusieurs lignes. Les valeurs insérées dans chaque colonne de la table peuvent être explicitement spécifiées ou les résultats d’une requête.

Voir aussi :

INSERT (multitable)

Dans ce chapitre :

Syntaxe

INSERT [ OVERWRITE ] INTO <target_table> [ ( <target_col_name> [ , ... ] ) ]
                                         { { VALUES ( { <value> | DEFAULT | NULL } [ , ... ] ) [ , ( ... ) ] } | <query> }

Paramètres requis

table_cible

Spécifie la table cible dans laquelle insérer les lignes. La même table peut être référencée plus d’une fois (dans des clauses WHEN distinctes).

VALUES ( valeur | DEFAULT | NULL [ , ... ] ) [ , ( ... ) ] ou . requête

Spécifie une ou plusieurs valeurs à insérer dans les colonnes correspondantes de la table cible. Les valeurs peuvent être soit les résultats d’une requête, soit explicitement spécifiées (en utilisant une clause VALUES) :

  • Pour une requête, spécifiez une instruction SELECT qui retourne les valeurs à insérer dans les colonnes correspondantes. Ceci vous permet d’insérer des lignes dans une table cible à partir d’une ou plusieurs tables sources.

  • Dans une clause VALUES, vous pouvez spécifier ce qui suit :

    • valeur : Insère la valeur explicitement spécifiée.

    • DEFAULT: Insère la valeur par défaut de la colonne correspondante dans la table cible.

    • NULL: Insère une valeur NULL .

    Chaque valeur de la clause doit être séparée par une virgule. Vous pouvez insérer plusieurs lignes en spécifiant des ensembles de valeurs supplémentaires dans la clause. Pour plus de détails, voir Notes sur l’utilisation (dans ce chapitre).

Paramètres facultatifs

OVERWRITE

Spécifie de tronquer la table cible avant de l’insérer dans la table, tout en conservant les privilèges de contrôle d’accès sur la table.

Les instructions INSERT avec OVERWRITE peuvent être traitées dans le cadre de la transaction en cours, en évitant les instructions DDL qui valident une transaction, telles que :

DROP TABLE t;
CREATE TABLE t AS SELECT * FROM ... ;

Par défaut : Aucune valeur (la table cible n’est pas tronquée avant d’effectuer les insertions)

( nom_col_cible [ , ... ] )

Spécifie une ou plusieurs colonnes de la table cible dans lesquelles les valeurs correspondantes sont insérées. Le nombre de colonnes cibles spécifié doit correspondre au nombre de valeurs ou de colonnes spécifiées (si les valeurs sont les résultats d’une requête) dans la clause VALUES.

Par défaut : Aucune valeur (toutes les colonnes de la table cible sont mises à jour)

Notes sur l’utilisation

  • En utilisant une seule commande INSERT, vous pouvez insérer plusieurs lignes dans une table en spécifiant des ensembles de valeurs supplémentaires séparés par des virgules dans la clause VALUES.

    Par exemple, la clause suivante insérerait 3 lignes dans une table à 3 colonnes, avec les valeurs 1, 2 et 3 dans les deux premières lignes et les valeurs 2, 3 et 4 dans la troisième ligne :

    VALUES ( 1, 2, 3 ) , ( 1, 2, 3 ) , ( 2, 3, 4)

  • Pour utiliser l’option OVERWRITE sur INSERT, votre rôle doit avoir le privilège DELETE sur la table car OVERWRITE supprimera les enregistrements existants dans la table.

Exemples

Insertion d’une seule ligne à l’aide d’une requête

Convertissez trois valeurs de chaînes en dates et insérez-les dans une seule ligne de la table mytable :

DESC TABLE mytable;

+------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name | type             | kind   | null? | default | primary key | unique key | check | expression | comment |
|------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| COL1 | DATE             | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| COL2 | TIMESTAMP_NTZ(9) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| COL3 | TIMESTAMP_NTZ(9) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+

INSERT INTO mytable
  SELECT TO_DATE('2013-05-08T23:39:20.123'), TO_TIMESTAMP('2013-05-08T23:39:20.123'), TO_TIMESTAMP('2013-05-08T23:39:20.123');

SELECT * FROM mytable;

+------------+-------------------------+-------------------------+
| COL1       | COL2                    | COL3                    |
|------------+-------------------------+-------------------------|
| 2013-05-08 | 2013-05-08 23:39:20.123 | 2013-05-08 23:39:20.123 |
+------------+-------------------------+-------------------------+

Semblable à l’exemple précédent, mais précisez de ne mettre à jour que les première et troisième colonnes de la table :

INSERT INTO mytable (col1, col3)
  SELECT TO_DATE('2013-05-08T23:39:20.123'), TO_TIMESTAMP('2013-05-08T23:39:20.123');

SELECT * FROM mytable;

+------------+-------------------------+-------------------------+
| COL1       | COL2                    | COL3                    |
|------------+-------------------------+-------------------------|
| 2013-05-08 | 2013-05-08 23:39:20.123 | 2013-05-08 23:39:20.123 |
| 2013-05-08 | NULL                    | 2013-05-08 23:39:20.123 |
+------------+-------------------------+-------------------------+

Insertion de plusieurs lignes à l’aide de valeurs spécifiées explicitement

Insérez deux lignes de données dans la table employees en fournissant les deux ensembles de valeurs dans une liste séparée par des virgules dans la clause VALUES :

SELECT * FROM employees;

+------------+-----------+----------------+---------------+-------------+
| FIRST_NAME | LAST_NAME | WORKPHONE      | CITY          | POSTAL_CODE |
|------------+-----------+----------------+---------------+-------------|
| May        | Franklin  | 1-650-249-5198 | San Francisco | 94115       |
| Gillian    | Patterson | 1-650-859-3954 | San Francisco | 94115       |
+------------+-----------+----------------+---------------+-------------+

INSERT INTO employees
  VALUES
  ('Lysandra','Reeves','1-212-759-3751','New York',10018),
  ('Michael','Arnett','1-650-230-8467','San Francisco',94116);

SELECT * FROM employees;

+------------+-----------+----------------+---------------+-------------+
| FIRST_NAME | LAST_NAME | WORKPHONE      | CITY          | POSTAL_CODE |
|------------+-----------+----------------+---------------+-------------|
| May        | Franklin  | 1-650-249-5198 | San Francisco | 94115       |
| Gillian    | Patterson | 1-650-859-3954 | San Francisco | 94115       |
| Lysandra   | Reeves    | 1-212-759-3751 | New York      | 10018       |
| Michael    | Arnett    | 1-650-230-8467 | San Francisco | 94116       |
+------------+-----------+----------------+---------------+-------------+

Dans des insertions à plusieurs lignes, assurez-vous que les types de données des valeurs insérées sont cohérents d’une ligne à l’autre, car le serveur considère le type de données de la première ligne comme un guide. Ce qui suit échouera parce que le type de données de la valeur de la deuxième ligne est différent du type de données de la valeur de la première ligne, même si les deux valeurs peuvent être forcées sur VARCHAR, qui est le type de données de la colonne dans la table :

CREATE TABLE t1 (v VARCHAR);

-- works as expected.
INSERT INTO t1 (v) VALUES
   ('three'),
   ('four');

-- Fails with error "Numeric value 'd' is not recognized"
-- even though the data type of 'd' is the same as the
-- data type of the column v.
INSERT INTO t1 (v) VALUES
   (3),
   ('d');

Insertion de plusieurs lignes à l’aide d’une requête

Insérez plusieurs lignes de données de la table contractors dans la table employees :

  • Sélectionnez uniquement les lignes où la colonne worknum contient l’indicatif régional 650 .

  • Insérez une valeur NULL dans la colonne city .

SELECT * FROM employees;

+------------+-----------+----------------+---------------+-------------+
| FIRST_NAME | LAST_NAME | WORKPHONE      | CITY          | POSTAL_CODE |
|------------+-----------+----------------+---------------+-------------|
| May        | Franklin  | 1-650-249-5198 | San Francisco | 94115       |
| Gillian    | Patterson | 1-650-859-3954 | San Francisco | 94115       |
| Lysandra   | Reeves    | 1-212-759-3751 | New York      | 10018       |
| Michael    | Arnett    | 1-650-230-8467 | San Francisco | 94116       |
+------------+-----------+----------------+---------------+-------------+

SELECT * FROM contractors;

+------------------+-----------------+----------------+---------------+----------+
| CONTRACTOR_FIRST | CONTRACTOR_LAST | WORKNUM        | CITY          | ZIP_CODE |
|------------------+-----------------+----------------+---------------+----------|
| Bradley          | Greenbloom      | 1-650-445-0676 | San Francisco | 94110    |
| Cole             | Simpson         | 1-212-285-8904 | New York      | 10001    |
| Laurel           | Slater          | 1-650-633-4495 | San Francisco | 94115    |
+------------------+-----------------+----------------+---------------+----------+

INSERT INTO employees(first_name, last_name, workphone, city,postal_code)
  SELECT
    contractor_first,contractor_last,worknum,NULL,zip_code
  FROM contractors
  WHERE CONTAINS(worknum,'650');

SELECT * FROM employees;

+------------+------------+----------------+---------------+-------------+
| FIRST_NAME | LAST_NAME  | WORKPHONE      | CITY          | POSTAL_CODE |
|------------+------------+----------------+---------------+-------------|
| May        | Franklin   | 1-650-249-5198 | San Francisco | 94115       |
| Gillian    | Patterson  | 1-650-859-3954 | San Francisco | 94115       |
| Lysandra   | Reeves     | 1-212-759-3751 | New York      | 10018       |
| Michael    | Arnett     | 1-650-230-8467 | San Francisco | 94116       |
| Bradley    | Greenbloom | 1-650-445-0676 | NULL          | 94110       |
| Laurel     | Slater     | 1-650-633-4495 | NULL          | 94115       |
+------------+------------+----------------+---------------+-------------+

Insérez plusieurs lignes de données de la table contractors dans la table employees à l’aide d’une expression de table courante :

INSERT INTO employees (first_name,last_name,workphone,city,postal_code)
  WITH cte AS
    (SELECT contractor_first AS first_name,contractor_last AS last_name,worknum AS workphone,city,zip_code AS postal_code
     FROM contractors)
  SELECT first_name,last_name,workphone,city,postal_code
  FROM cte;

Insérez les colonnes des deux tables (emp_addr, emp_ph) dans une troisième table (emp) en utilisant INNER JOIN sur la colonne id dans les tables sources :

INSERT INTO emp (id,first_name,last_name,city,postal_code,ph)
  SELECT a.id,a.first_name,a.last_name,a.city,a.postal_code,b.ph
  FROM emp_addr a
  INNER JOIN emp_ph b ON a.id = b.id;

Insertion de plusieurs lignes pour des données JSON

Insérez deux objets JSON dans une colonne VARIANT d’une table :

INSERT INTO prospects
  SELECT PARSE_JSON(column1)
  FROM VALUES
  ('{
    "_id": "57a37f7d9e2b478c2d8a608b",
    "name": {
      "first": "Lydia",
      "last": "Williamson"
    },
    "company": "Miralinz",
    "email": "lydia.williamson@miralinz.info",
    "phone": "+1 (914) 486-2525",
    "address": "268 Havens Place, Dunbar, Rhode Island, 7725"
  }')
  , ('{
    "_id": "57a37f7d622a2b1f90698c01",
    "name": {
      "first": "Denise",
      "last": "Holloway"
    },
    "company": "DIGIGEN",
    "email": "denise.holloway@digigen.net",
    "phone": "+1 (979) 587-3021",
    "address": "441 Dover Street, Ada, New Mexico, 5922"
  }');

Insertion à l’aide de l’écrasement

Cet exemple utilise INSERT avec OVERWRITE pour reconstruire la table sf_employees à partir de employees après l’ajout de nouveaux enregistrements à la table employees .

Voici les données initiales des deux tables :

SELECT * FROM employees;
+------------+-----------+----------------+---------------+-------------+
| FIRST_NAME | LAST_NAME | WORKPHONE      | CITY          | POSTAL_CODE |
|------------+-----------+----------------+---------------+-------------|
| May        | Franklin  | 1-650-111-1111 | San Francisco | 94115       |
| Gillian    | Patterson | 1-650-222-2222 | San Francisco | 94115       |
| Lysandra   | Reeves    | 1-212-222-2222 | New York      | 10018       |
| Michael    | Arnett    | 1-650-333-3333 | San Francisco | 94116       |
+------------+-----------+----------------+---------------+-------------+
SELECT * FROM sf_employees;
+------------+-----------+----------------+---------------+-------------+
| FIRST_NAME | LAST_NAME | WORKPHONE      | CITY          | POSTAL_CODE |
|------------+-----------+----------------+---------------+-------------|
| Martin     | Short     | 1-650-999-9999 | San Francisco | 94115       |
+------------+-----------+----------------+---------------+-------------+

Cette instruction insère dans la table sf_employees à l’aide de la clause OVERWRITE :

INSERT OVERWRITE INTO sf_employees
  SELECT * FROM employees
  WHERE city = 'San Francisco';

Étant donné que INSERT a utilisé l’option OVERWRITE, les anciennes lignes de sf_employees ont disparu :

SELECT * FROM sf_employees;
+------------+-----------+----------------+---------------+-------------+
| FIRST_NAME | LAST_NAME | WORKPHONE      | CITY          | POSTAL_CODE |
|------------+-----------+----------------+---------------+-------------|
| May        | Franklin  | 1-650-111-1111 | San Francisco | 94115       |
| Gillian    | Patterson | 1-650-222-2222 | San Francisco | 94115       |
| Michael    | Arnett    | 1-650-333-3333 | San Francisco | 94116       |
+------------+-----------+----------------+---------------+-------------+