LOGIN
CONTACT
RGPD

SQL INSERT INTO - Requête d'insertion en SQL et BULK

Requête SQL INSERT INTO - découvrez comment utiliser la commande SQL INSERT INTO : la requête d'insertion de données en SQL. Cas pratiques avec des exemples.

  Publié le

Découverte de la commande SQL insert into

 

En SQL l'insertion de données s'effectue grâce à la commande sql INSERT INTO. En effet, la commande insert into permet d'ajouter un nouvel enregistrement dans la table de notre base de données (que le moteur de utilisé soit MariaDB,Mysql,PostGreSQL,Transact-SQL, Oracle ou toute autre SGBD).

En SQL, INSERT INTO est la commande la plus couramment utilisée en SQL pour manipuler nos données de même que les trois autres commandes fondamentales en SQL, à savoir SELECT,UPDATE et DELETE.

INSERT INTO va donc nous servir à créer une ou plusieurs nouvelles entrées. Nous aborderons également la méthode BULK INSERT et la commande LOAD DATA INFILE, qui permet l'ajout massif de données depuis un fichier CSV ou TXT simplement et rapidement.

 

Syntaxe de base de la commande SQL insert into

 

Pour créer un nouvel enregistrement en base la commande INSERT INTO s'utilise de la manière suivante :

 

INSERT INTO ma_table (colonne_1, colonne_2, colonne_3) VALUES ('première', 'seconde ', 'troisième')

On précise la commande INSERT INTO suivi du nom de chacune des colonnes, puis on désigne chaque valeur qui lui sera assignée encapsulées par des guillemets (ou des apostrophes). Cette syntaxe à l'avantage de ne pas nous obliger à remplir chacune des colonnes. En effet nous pouvons parfaitement effectuer la commande SQL INSERT suivante :

 

INSERT INTO ma_table (colonne_2, colonne_3) VALUES ('seconde', 'troisième')

En supposant que la colonne_1 soit une clé primaire auto incrémentale, cela nous évite d'écrire :

 

INSERT INTO ma_table (colonne_1,colonne_2, colonne_3) VALUES (NULL,'seconde', 'troisième')

La valeur de notre première colonne sera automatiquement assignée par la base de données.

 

Il est également possible d'effectuer une commande SQL INSERT INTO sans préciser les colonnes, que nous désirons utiliser dans ce cas, le moteur de SGBD interprétera implicitement l'ordre des colonnes de la table. Nous pouvons également inclure des fonctions natives au moteur SQL directement dans notre requête. Ici nous utiliserons la fonction NOW() qui retourne la date et l'heure lors de l'éxécution de la requête SQL.

 

Pour une table appelée users avec trois colonnes :

user log comment

 

 

la requête INSERT propre à ce format sera :

 

INSERT INTO users VALUES ('john', NOW(),'mysql c'est super !')

Ce qui reviendrait à la requête SQL INSERT INTO :

 

INSERT INTO users (user,log,comment) VALUES ('john', NOW(),'mon super commentaire')

Syntaxe avancée avec SELECT de la commande SQL insert into

 

Le moteur de base de données nous permet d'effectuer une commande SQL INSERT INTO directement depuis une requête SELECT.

Il est alors possible de copier le contenu d'une ligne spécifique d'une table SQL dans une autre.

INSERT INTO ma_table_1 SELECT colonne_1,colonne_2,colonne_2 FROM ma_table_2 WHERE conditions

SQL INSERT INTO permet aussi d'effectuer des insertions par sous requête. Bien que ce genre de commande soit décriée pour une question de performance, elles peuvent s'avérer utiles dans certains cas singuliers. La syntaxe de ces requêtes répondra à ce modèle :

 

INSERT INTO ma_table_1 (colonne_1,colonne_2, colonne_3) VALUES ((SELECT colonne_1 FROM ma_table_2 WHERE conditions LIMIT 1),'seconde valeur', 'troisième valeur')

À noter qu'il est impératif que la sous requête :

 

(SELECT colonne_1 FROM ma_table_2 WHERE conditions LIMIT 1)

Ne retourne qu'un seul et unique résultat d'une seule colonne. Sans quoi le moteur SQL retournera une erreur et l'enregistrement ne sera pas inclus dans la table lors de la commande INSERT INTO.

Syntaxe avancée de la commande SQL insert into avec multiples entrées

 

Il est possible de créer de multiples insertions d'entrées dans notre base de données, en n'écrivant qu'une fois l'entête de la commande INSERT INTO et en lui adjoignant plusieurs lots de VALUES toutes séparées par une simple virgule. En supposant à nouveau notre table users à trois colonnes, la syntaxe de notre requête SQL avancée devient donc :

 

INSERT INTO users (user,log) VALUES ('john',NOW()),('pierre',NOW()),('paul',NOW()),('doe',NOW()) ;

Cette requête INSERT INTO produira quatre enregistrements dans notre table users. Sachez qu'il est tout à fait possible de profiter de l'ordre implicite des colonnes de la table et donc de ne pas les préciser. Ceci nous obligera à assigner une valeur à chaque colonne, sans quoi la requête provoquera une erreur du SGBD.

 

De même, nous pouvons également appliquer ce que nous avons vu avec les insertions via sous-requête (bien que cela reste toujours discutable sur le plan de la performance).

Syntaxe avancée de la commande SQL INSERT INTO avec CAST

Pour garantir le bon fonctionnement de la requête sql insert into, il est parfois nécessaire de réaliser des conversions de données. En SQL cette action s'effectue à l'aide de la commande SQL CAST. Il est ainsi possible de convertir une chaine de caractère en date, ou en entier, ou encore un entier en chaine de caractères.

Pour réaliser une conversion de typage d'un format vers un autre il suffit d'utiliser la fonction CAST, de lui assigner en paramètre la donnée que nous souhaitons convertir suivi de AS le format attendu (DATETIME,DATE,VARCHAR, SIGNED INTEGER). Comme le montre l'exemple suivant de la bonne utilisation de la requête SQL insert into avec CAST.

 

INSER INTO users (user,log) VALUES ('john',CAST('2020-12-13 12:06:01' AS DATETIME))

Comment copier une table dans une autre avec la commande SQL INSERT INTO ?

 

INSERT INTO table_2 (colonne_1, colonne_2, colonne_3)
	SELECT colonne_1, colonne_2, colonne_3
	FROM table_1
	WHERE conditions;

Comment ajouter une donnée inéxistante mais ne rien faire si cette enregistrement est déja stocké dans la table avec INSERT IGNORE INTO ?

La commande INSERT IGNORE INTO se comporte comme INSERT INTO à la différence qu'elle permet de ne pas ajouter l'enregistrement si un enregistrement avec la même clé primaire existe déjà. La syntaxe de cette commande est très proche d'un INSERT INTO classique. Il est impératif que les enregistrements de la table soient soumis à une clé primaire, ou une contrainte d'unicité.

INSERT IGNORE INTO table_1 (colonne_1_et_PK,colonne_2,colonne_3) VALUES(valeur_1_pk_ou_null,valeur_2,valeur_3)

Comment mettre à jour un enregistrement ou le créer à l'aide de la commande SQL REPLACE INTO ?

La commande SQL REPLACE INTO permet de mettre à jour une entrée, ou de la créer si elle n'existe pas. Cette commande à plusieurs syntaxes, mais la plus simple (et surtout la plus logique compte tenu de l'application de celle-ci) reste la syntaxe qui approche de la commande UPDATE.

 

REPLACE INTO table_1 
SET 
colonne_1_et_PK=valeur_1_pk_ou_null,
colonne_2=valeur_2

Syntaxe avancée SQL avec la commande BULK INSERT EN T-SQL

 

Pour charger des lots d'enregistrements issus de fichiers volumineux en csv ou en txt de la façon la plus optimisée possible. Certains moteurs de gestion de base de données SQL (dont le Transact-SQL, ORACLE,SQL), on ajoute à la liste de leur commande, BULK.

 

Une requête BULK vise à charger des ensembles de lignes directement depuis un jeu de données (aussi appelée dataset) sans avoir besoin de créer chaque requête manuellement.

 

La syntaxe d'une requête BULK est rapide et simple :

 

BULK INSERT ma_table FROM 'c:\monfichier.csv' ;

Syntaxe avancée SQL avec la commande LOAD DATA INFILE

 

D'autre langage comme le MySQL et le MariaDB on répondu en ajoutant la commande LOAD DATA INFILE elle se comporte de la même façon que la requête BULK vue plus haut.

 

LOAD DATA INFILE '/monfichier.csv/' INTO TABLE ma_table ;

De nombreux paramètres existent, mais les deux plus importants sont FIELDS TERMINATED BY qui permet de définir que chaque champ est séparé par un point virgule.

 

LOAD DATA INFILE '/monfichier.csv/' INTO TABLE ma_table FIELDS TERMINATED BY ' ;' ;

Et le paramètre IGNORE X LINES qui sert à déclaré que le fichier CSV dispose d'un en tête et que celui-ci doit être ignoré par la commande lors du traitement du lot des données.

 

LOAD DATA INFILE '/monfichier.csv/' INTO TABLE ma_table FIELDS TERMINATED BY ' ;' IGNORE 1 LINES ;

SQL INSERT INTO MYSQL avec l'objet PDO en PHP

Pour nous connecter à une base de données MySQL en PHP nous utiliserons l'objet PDO.

L'objet PDO est une classe PHP qui permet de créer une interface avec la base de données, ici MySQL. Cet objet simplifie les accès à la base de données et ajoute une couche de sécurité supplémentaire lors de la manipulation des données.

 

Dans l'exemple suivant nous allons effectuer la commande SQL INSERT INTO dans notre table user (qui contient toujours ses trois colonnes user,log,comment). Le tout en utilisant l'objet PDO et les requêtes préparées pour se protéger contre d'éventuelles injections SQL. Nous assignons au champ user la valeur "john" et au champ comment la valeur "Commentaire sur l'utilisation", le champ log sera quant à lui rempli automatiquement par la valeur qui sera retournée par la fonction NOW() cette fonction retournant la date et l'heure du moment ou la requête est exécutée.

 

Pour ce faire nous utilisons les marqueurs nommés :user ainsi que le marqueur nommé :comment dans notre requête SQL. Lors de la préparation de la requête via la méthode prepare(), ceux-ci serons remplacer par les valeurs echappés et sécurisés.

 

$dba = new PDO("mysql:host=127.0.0.1;dbname=DataBase","user","password",array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION )); 

try{
    $dic = array("user" => "john","comment"=>"Commentaire sur l'utilisation");
	$query = "INSERT INTO user VALUES(:user,NOW(),:comment)";

	$request= $dba->prepare($query);
	$request->execute($dic);
}
catch(PDOException $pdo_e){
  //En cas d'erreur MYSQL
}
catch(Exception $e){
  //Autres erreurs
}
finally{
	$dba = null;
}

Si vous souhaitez davantage d'informations sur les bonnes pratiques lors de l'utilisation de l'objet PDO nous vous invitons à lire là publication : objet PDO PHP MYSQL - protection contre les injections SQL.

INSERT en MYSQL grâce au Csharp

 

Pour effectuer une requête INSERT INTO MYSQL en Csharp, il faut dans un premier temps récupérer la DLL (MySql.Data) et l'ajouter en référence à notre projet. Une fois que c'est fait, il faudra ouvrir la connexion à notre base de données, réaliser notre commande cela se fait en quelques lignes de codes :

 

using MySql.Data.MySqlClient;

namespace Test{

	class Program{
		static void Main(string[] args)
        {
			String Ccx = @"SERVER=127.0.0.1;DATABASE=basecible;PORT=3306;UID=username;PASSWORD=passwordaccess;"
			MySqlConnection Dba = new MySqlConnection(Ccx);
			Dba.Open();

			MySqlCommand Cmd = new MySqlCommand("INSER INTO users VALUES('john',NOW(),'super commentaire')", Dba,null); 
			Cmd.CommandTimeout = 300;
			Cmd.ExecuteNonQuery();
			Dba.Close();
		}
	}
}

A noter qu'aucun système ne permet de préparer et donc de sécuriser votre requête comme c'est le cas en PHP, il vous faudra donc vous en occuper en amont de l'éxécution de la requête SQL.

INSERT SQL en Csharp

 

Pour réaliser une requête INSERT INTO SQL en Csharp, l'approche est très similaire à celle pour le MySQL. On définit une chaine de connexion, puis on ouvre la connexion avec la base de données SQL. On écrit la requête, on l'exécute, on ferme la connexion à la base de données.

 

using System.Data;
using System.Data.SqlClient;

namespace Test{

	class Program{
		static void Main(string[] args)
        {
			String Ccx = @"Data Source=127.0.0.1\WEBSQL;Initial Catalog=Base_Cible;Persist Security Info=True;User ID=userDemo;Password=demo"
			SqlConnection Dba = new SqlConnection(Ccx);
			Dba.Open();

			SqlCommandCmd = new SqlCommand("INSER INTO users VALUES('john',NOW(),'super commentaire')", Dba,null); 
			Cmd.CommandTimeout = 300;
			Cmd.ExecuteNonQuery();
			Dba.Close();
		}
	}
}

 

Vous savez maintenant comment utiliser les requêtes d'insertion en SQL aussi bien en C# qu'en PHP. Nous vous invitons maintenant à la plus grande prudence lors de la mise en oeuvre pour des applications publiques, car cette publication ne traite pas des aspects sécuritaires des INSERT en SQL.