Insérez efficacement des données dans vos bases de données grâce à la clause INSERT INTO

La collecte fait partie des premières étapes du traitement des données. C’est lors de cette collecte que l’on stocke ces données dans un système préalablement établi. Si l’on utilise le langage SQL pour cela, c’est l’instruction INSERT INTO qui est utilisée pour l’ajout de données dans une table d’une base de données quelconque.

Dans cet article, vous allez découvrir les cas d’utilisation normal d’INSERT INTO et comment insérer manuellement des données. Alors, sans plus tarder, commençons !

La syntaxe de l’instruction INSERT INTO

Tout d’abord, commençons par sa syntaxe qui ressemble à ceci :

INSERT INTO table_name (column_a, column_b)
VALUES ("value_a", "value_b");

Vous pouvez remarquer qu’INSERT INTO est suivi du nom de la table et d’une liste facultative de colonnes de donnée que l’on retrouve dans cette dernière. Ensuite, on trouve le mot clé VALUES et les valeurs à insérer dans chacun des champs spécifiés dans la liste des colonnes de données.

Insertion d’une ligne

De façon générale, il existe deux méthodes, c’est-à-dire deux syntaxes d’insertion des données dans une base de données :

•  Insérer une ligne tout en fournissant les informations sur chaque colonne de cette ligne en ordre ;
• Insérer une ligne en précisant les colonnes que l’on souhaite compléter. Il est également possible d’ajouter une ligne donnant juste quelques informations sur une colonne.

Insérer une ligne en spécifiant toutes les colonnes

Pour cette première méthode, la syntaxe est la suivante :

INSERT INTO table VALUES ('valeur 1', 'valeur 2', ...)

Pour l’illustration, considérons la table employe suivante :

Essayons d’ajouter une ligne à cette table avec cette requête :

INSERT INTO `employe` VALUES ('115', 'Ange', 'Ahou', '6800', '600')

Vous pouvez remarquer la ligne ajoutée indiquée par la flèche rouge ci-dessus.

Cette syntaxe possède les avantages et inconvénients suivants : 

• En l’utilisant, vous êtes dans l’obligation de remplir toutes les données en respectant l’ordre des colonnes ;
• Les colonnes doivent respecter un ordre précis, sinon certaines valeurs seront attribuées à des colonnes qui ne leur sont pas dédiées.
• Cependant, les fautes de frappe sont limitées, car il n’y a pas de nom. Vous pouvez aussi changer de nom de colonne sans avoir à changer la requête.

Insérer une ligne en spécifiant les colonnes

La syntaxe de celle-ci est très similaire à la précédente, mais ici, il faut indiquer les noms de colonne. Reprenons notre exemple en utilisant cette syntaxe :

INSERT INTO `employe`(`numero`, `nom`, `Prenom`, `salaire`, `dept_id`) VALUES ('116', 'Ange', 'Ahou', '6800', '600')

Note : Il faut savoir qu’on n’est pas obligé de renseigner toutes les colonnes et l’ordre de ces dernières n’est pas nécessaire.

Insertion de plusieurs lignes à la fois

Lorsque l’on traite un volume de données très important, comme c’est le cas dans le Big Data, avoir la possibilité d’effectuer une insertion multiple est nécessaire. Le langage SQL nous permet justement de réaliser cela à l’aide d’une instruction INSERT INTO.

Pour ajouter plusieurs lignes dans une table grâce à une seule requête SQL, il faut écrire cette instruction :

INSERT INTO employe (numero,prenom, nom, salaire, dept_id)
 VALUES
 (117,'Rébecca', 'Armand',9000, 500),
 (118,'Aimée', 'Hebert', 18000, 700),
 (118,'Marielle', 'Ribeiro', 1500, 850),
 (119,'Hilaire', 'Savary', 17000, 580);

Vous avez sans doute remarqué que tous les champs sont mis entre guillemets. Il faut savoir que lorsque le champ à remplir est de type INT ou BIGINT, on n’a pas besoin de mettre les guillemets. Par contre, s’il s’agit du type VARCHAR ou TEXT, les guillemets sont obligatoires.

Insertion de données d’une table à une autre avec INSERT INTO SELECT

Oui, c’est possible de récupérer des données venant d’une table et de les transférer dans une autre en les ajoutant à cette dernière.

Grâce à l’instruction SQL suivante, vous pouvez effectuer cette opération :

INSERT INTO table1 (colonne1, colonne2, colonne3, …)
SELECT colonne1, colonne2, colonne3, …
FROM table2

Voici les différentes tâches effectuées par cette instruction : 

• Premièrement, on sélectionne certaines colonnes de la table dans laquelle vous souhaitez copier les données grâce à l’instruction SELECT ;
• Puis, on insère ces données dans les colonnes de la deuxième table avec INSERT INTO.

Note : La structure des colonnes des deux tables doit être identique.

Considérons par exemple les enregistrements de la table nommée employe suivante :

On souhaite insérer les données de la table employe à une autre table nommée client qui est vide :

Pour ce faire, nous allons exécuter la requête suivante :

INSERT INTO client
   	SELECT *
   	FROM employe;

Insertion des N premières lignes d’une table source à une autre

Si vous souhaitez insérer les N premières lignes d’une table source à une autre, il faut utiliser la clause TOP avec l’instruction INSERT INTO SELECT. La syntaxe est la suivante : 

INSERT TOP(N) INTO tble_source
(colonne_1, colonne_2,…
)
   	SELECT colonne_1, colonne_2,…
   	FROM table_destination;

INSERT INTO SELECT avec la clause join

Avec la clause JOIN, vous avez la possibilité d’obtenir des données venant de différentes tables. Si par exemple vous souhaitez obtenir des données de plusieurs tables pour ensuite les insérer dans une autre, la clause JOIN pourra vous aider.

Dans l’exemple suivant, on va utiliser la base de données AdventureWorks2017. L’instruction ci-dessous va créer une table :

CREATE TABLE [HumanResources].[EmployeeData](
  [FirstName] [dbo].[Name] NOT NULL,
  [MiddleName] [dbo].[Name] NULL,
  [LastName] [dbo].[Name] NOT NULL,
  [Suffix] [nvarchar](10) NULL,
  [JobTitle] [nvarchar](50) NOT NULL,
  [PhoneNumber] [dbo].[Phone] NULL,
  [PhoneNumberType] [dbo].[Name] NULL,
  [EmailAddress] [nvarchar](50) NULL,
  [City] [nvarchar](30) NOT NULL,
  [StateProvinceName] [dbo].[Name] NOT NULL,
  [PostalCode] [nvarchar](15) NOT NULL,
  [CountryRegionName] [dbo].[Name] NOT NULL
) ON [PRIMARY]
GO

Les données à insérer dans cette table proviennent de la jointure de plusieurs autres tables. La requête à exécuter pour insérer ces données est la suivante :

INSERT INTO HumanResources.EmployeeData
SELECT p.[FirstName],
   	p.[MiddleName],
   	p.[LastName],
   	p.[Suffix],
   	e.[JobTitle],
   	pp.[PhoneNumber],
   	pnt.[Name] AS [PhoneNumberType],
   	ea.[EmailAddress],
   	a.[City],
   	sp.[Name] AS [StateProvinceName],
   	a.[PostalCode],
   	cr.[Name] AS [CountryRegionName]
FROM [HumanResources].[Employee] e
 	INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]
 	INNER JOIN [Person].[BusinessEntityAddress] bea ON bea.[BusinessEntityID] = e.[BusinessEntityID]
 	INNER JOIN [Person].[Address] a ON a.[AddressID] = bea.[AddressID]
 	INNER JOIN [Person].[StateProvince] sp ON sp.[StateProvinceID] = a.[StateProvinceID]
 	INNER JOIN [Person].[CountryRegion] cr ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
 	LEFT OUTER JOIN [Person].[PersonPhone] pp ON pp.BusinessEntityID = p.[BusinessEntityID]
 	LEFT OUTER JOIN [Person].[PhoneNumberType] pnt ON pp.[PhoneNumberTypeID] = pnt.[PhoneNumberTypeID]
 	LEFT OUTER JOIN [Person].[EmailAddress] ea ON p.[BusinessEntityID] = ea.[BusinessEntityID];
GO

INSERT INTO SELECT avec une expression de table commune

Pour simplifier les jointures des colonnes les plus compliquées, il faut utiliser ce qu’on appelle les expressions de table commune (CTE). Précédemment, c’est JOIN qui était employé avec l’instruction SELECT pour l’insertion des données dans la table SQL. Mais dans ce cas-ci, on va l’effectuer avec CTE qui est en réalité une requête que l’on utilise en tant que table.

Dans un CTE, le code peut être divisé en deux parties : 

1. Le CTE est d’abord défini par la clause WITH avant l’instruction SELECT, INSERT, UPDATE ou DELETE ;
2. Une fois que le CTE est défini, il faut prendre la référence du CTE de la même manière qu’une table SQL.

WITH EmployeeData_Temp([FirstName],
                   	[MiddleName],
                   	[LastName],
                   	[Suffix],
                   	[JobTitle],
                   	[PhoneNumber],
                   	[PhoneNumberType],
                   	[EmailAddress],
                   	[City],
                   	[StateProvinceName],
                   	[PostalCode],
                   	[CountryRegionName])
 	AS (
 
 	SELECT p.[FirstName],
            p.[MiddleName],
            p.[LastName],
            p.[Suffix],
            e.[JobTitle],
            pp.[PhoneNumber],
            pnt.[Name] AS [PhoneNumberType],
            ea.[EmailAddress],
            a.[City],
            sp.[Name] AS [StateProvinceName],
            a.[PostalCode],
            cr.[Name] AS [CountryRegionName]
 	FROM [HumanResources].[Employee] e
          INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]
          INNER JOIN [Person].[BusinessEntityAddress] bea ON bea.[BusinessEntityID] = e.[BusinessEntityID]
          INNER JOIN [Person].[Address] a ON a.[AddressID] = bea.[AddressID]
          INNER JOIN [Person].[StateProvince] sp ON sp.[StateProvinceID] = a.[StateProvinceID]
          INNER JOIN [Person].[CountryRegion] cr ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
          LEFT OUTER JOIN [Person].[PersonPhone] pp ON pp.BusinessEntityID = p.[BusinessEntityID]
          LEFT OUTER JOIN [Person].[PhoneNumberType] pnt ON pp.[PhoneNumberTypeID] = pnt.[PhoneNumberTypeID]
          LEFT OUTER JOIN [Person].[EmailAddress] ea ON p.[BusinessEntityID] = ea.[BusinessEntityID])
 
INSERT INTO HumanResources.EmployeeData
            SELECT *
            FROM EmployeeData_Temp;
GO

Instruction INSERT INTO SELECT avec une table variable

Les tables variables et temporaires sont utilisées de la même manière à savoir à manipuler des données de manière temporaire. Cependant, les tables variables, malgré le fait qu’elles sont considérées comme inefficaces, sont utilisées lorsqu’une table temporaire n’est pas forcément nécessaire.

Prenons par exemple la requête suivante :

DECLARE @TableVar table(  
    [JobTitle] [nvarchar](50) NOT NULL,
  [BirthDate] [date] NOT NULL,
  [MaritalStatus] [nchar](1) NOT NULL,
  [Gender] [nchar](1) NOT NULL,
  [HireDate] [date] NOT NULL,
  [SalariedFlag] [dbo].[Flag] NOT NULL,
  [VacationHours] [smallint] NOT NULL,
  [SickLeaveHours] [smallint] NOT NULL
  )
  
-- Insert values into the table variable.  
INSERT INTO @TableVar
    SELECT  
   [JobTitle]
      ,[BirthDate]
      ,[MaritalStatus]
      ,[Gender]
      ,[HireDate]
      ,[SalariedFlag]
      ,[VacationHours]
      ,[SickLeaveHours]
    FROM [AdventureWorks2017].[HumanResources].[Employee]
  
-- View the table variable result set.  
SELECT * FROM @TableVar;  
GO

Cette requête est divisée en trois parties :

1. La première partie est la création d’une variable table ;
2. La deuxième est l’insertion des données dans la table ;
3. Et enfin l’affichage de la table.

INSERT INTO SELECT DISTINCT

L’instruction INSERT INTO SELECT DISTINCT sert à copier des données d’une table qui existe déjà vers une nouvelle table. Après INSERT INTO, il faut mentionner le nom de la table cible puis sélectionner les colonnes à copier. Voici la syntaxe de cette instruction :

INSERT INTO table_cible SELECT DISTINCT organization,
    colonne_a_copie
FROM table_existante;

INSERT INTO dans une table avec des colonnes à valeurs par défaut

Passons directement par l’exemple suivant pour montrer la manière d’ajouter des lignes dans une table dont les colonnes disposent d’une valeur par défaut. C’est également valable pour celle qui génère automatiquement des valeurs.

CREATE TABLE dbo.T1   
(  
    colonne1 AS 'Compte colonne ' + colonne2,   
    colonne2 varchar(30)   
        CONSTRAINT default_name DEFAULT ('ma colonne '),  
    colonne3 rowversion,  
    colonne4 varchar(40) NULL  
);  
GO

Décortiquons un peu les attributs de cette création de tables :

• La valeur de colonne1 est générée par la somme d’une chaîne de caractère et de la valeur insérée dans la colonne2 ;
• colonne2 est définie par avec une contrainte par défaut.  Si aucune valeur n’est ajoutée pour cette colonne, on utilisera une valeur par défaut ;
• colonne3 a pour type rowversion et ce sont des nombres binaires uniques qui sont générés ;
• Et colonne4 n’a pas de valeur par défaut, mais peut prendre la valeur NULL si rien n’est spécifié.

Maintenant, si l’on souhaite ajouter des données dans cette table, cela se passe de cette manière :

INSERT INTO pays.T1 (colonne4)   
    VALUES ('Explicit value');  
INSERT INTO pays.T1 (colonne2, colonne4)   
    VALUES ('Explicit value', 'Explicit value');  
INSERT INTO pays.T1 (colonne2)   
    VALUES ('Explicit value');  
INSERT INTO T1 DEFAULT VALUES;   
GO  
SELECT colonne1, colonne2, colonne3, colonne4  
FROM pays.T1;  
GO  

Comme vous pouvez le constater, les différentes instructions INSERT ci-dessus n’ajoutent pas des valeurs dans toutes les colonnes. Quant à la dernière instruction INSERT, elle n’ajoute que les valeurs par défaut.

Insertion de données dans une table avec une colonne d’identité

La colonne identité est celle qui sert justement à identifier une ligne de données lors de la sélection ou de l’insertion. Par conséquent, cette colonne doit être unique.

Vous trouverez dans l’exemple qui suit les différentes manières d’insérer des valeurs dans une colonne identité : 

CREATE TABLE pays.T1 ( colonne1 int IDENTITY, colonne2 VARCHAR(30));  
GO  
INSERT T1 VALUES ('Row #1');  
INSERT T1 (colonne2) VALUES ('Row #2');  
GO  
SET IDENTITY_INSERT T1 ON;  
GO  
INSERT INTO T1 (colonne1,colonne2)   
    VALUES (-99, 'Explicit identity value');  
GO  
SELECT colonne1, colonne2  
FROM T1;  
GO

Dans les deux premières instructions INSERT, les valeurs identité des nouvelles lignes sont générées automatiquement. Mais à la troisième instruction INSERT, la propriété IDENTITY de la colonne est remplacée par l’instruction SET IDENTITY_INSERT. Elle insère ensuite une valeur spécifique dans la colonne identité. 

Insertion de données dans une colonne uniqueidentifier en utilisant NEWID()

Pour insérer des données dans une colonne de type uniqueidentifier, on utilise la fonction NEWID() que nous allons voir à travers cet exemple :

CREATE TABLE pays.T1   
(  
    colonne1 int IDENTITY,   
    colonne2 uniqueidentifier,  
);  
GO  
INSERT INTO pays.T1 (colonne2)   
    VALUES (NEWID());  
INSERT INTO T1 DEFAULT VALUES;   
GO  
SELECT colonne1, colonne2  
FROM pays.T1;

Dans ce cas-ci, on utilise la fonction NEWID() pour obtenir un GUID pour colonne2. À la différence des colonnes identité, les valeurs des colonnes qui sont de type uniqueidentifier ne sont pas générées automatiquement par le moteur de base de données. La deuxième instruction INSERT le montre bien.

Utilisation de la clause OUTPUT avec SQL INSERT

Dans cet exemple, la clause OUTPUT permet d’afficher les données entrées dans la table. La colonne ScrapReasonID est définie avec une propriété IDENTITY. Donc dans l’instruction INSERT, aucune valeur n’est spécifiée pour cette colonne. 

Par contre, la valeur générée par le moteur de base de données pour cette colonne se retrouve dans INSERTED.ScrapReasonID de la clause OUTPUT. Voici un exemple qui illustre cela :

DECLARE @MyTableVar table( NewScrapReasonID smallint,  
                           Name varchar(50),  
                           ModifiedDate datetime);  
INSERT Production.ScrapReason  
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate  
        INTO @MyTableVar  
VALUES (N'Operator error', GETDATE());  
  
--Display the result set of the table variable.  
SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;  
--Display the result set of the table.  
SELECT ScrapReasonID, Name, ModifiedDate   
FROM Production.ScrapReason;

Insertion dans une table externe créée avec PolyBase

Pour exporter des données de SQL Server vers Hadoop ou Azure Storage, il faut d’abord créer au préalable une table externe qui est liée au fichier ou au répertoire de destination. 
Lors de l’exportation, si le répertoire de destination n’existe pas l’instruction INSERT SELECT va le créer. Voyons cela avec cet exemple :

-- Create an external table.   
CREATE EXTERNAL TABLE [dbo].[FastCustomers2009] (  
        [FirstName] char(25) NOT NULL,   
        [LastName] char(25) NOT NULL,   
        [YearlyIncome] float NULL,   
        [MaritalStatus] char(1) NOT NULL  
)  
WITH (  
        LOCATION='/old_data/2009/customerdata.tbl',  
        DATA_SOURCE = HadoopHDP2,  
        FILE_FORMAT = TextFileFormat,  
        REJECT_TYPE = VALUE,  
        REJECT_VALUE = 0  
);  

-- Export data: Move old data to Hadoop while keeping 
-- it query-able via external table.  

INSERT INTO dbo.FastCustomer2009  
SELECT T.* FROM Insured_Customers T1 JOIN CarSensor_Data T2  
ON (T1.CustomerKey = T2.CustomerKey)  
WHERE T2.YearMeasured = 2009 and T2.Speed > 40;

Voilà, nous arrivons à la fin de cet article dans lequel vous avez pu voir tous les cas d’utilisation de l’instruction INSERT INTO. Vous pouvez maintenant non seulement ajouter des données dans une table, mais également varier le mode d’insertion afin qu’il puisse être adapté à vos besoins.

Si vous souhaitez apprendre plus d’instructions SQL, nous vous invitons à vous rendre sur notre blog, car nous avons plusieurs articles sur cela. Et si vous voulez approfondir vos connaissances en Big Data, vous pouvez télécharger gratuitement ce tutoriel sur Scala.