Parfois, il est nécessaire d’exporter ou d’importer d’importantes quantités de données vers ou depuis une base de données. Ces opérations sont appelées opérations d’import et d’export en masse ou respectivement bulk import et bulk export.
SQL Server fournit des outils pour réaliser de telles opérations d’import et d’export ainsi que des copies en masse : l’utilitaire BCP (BCP Utility), la fonction OPENROWSET(BULK), l’assistant d’importation et d’exportation de SQL Server, la commande BULK INSERT et la commande SELECT INTO.
Cet article explique comment copier une importante quantité de données à partir d’une table ou d’un ensemble de tables en utilisant les outils cités préalablement et en utilisant ApexSQL Script et ApexSQL Data Diff.
Copier des données en utilisant l’assistant d’import et d’export de SQL Server
L’assistant d’importation et d’exploration crée un package SSIS (SQL Server Integration Services) qui peut être programmé pour fonctionner selon certains critères et modifié en utilisant les outils de données SQL Server.
Pour démarrer l’assistant d’import-export, faites un clic droit sur la base de données et sélectionnez Tasks (Tâches) puis Export Data Command (Commande d’exportation de données):
- Dans la fenêtre Choose a Data Source (Choisir une source de données), connectez-vous à la base de données source. Des permissions suivantes sont nécessaires pour faire usage de l’utilitaire d’importation et d’exploration à partir de la source : lecture des données de la base de données ou du fichier et permission de la commande INSERT sur la base de données msdb pour sauvegarder le package SSIS
-
Dans la fenêtre Choose a destination (choisir une destination), connectez-vous à la base de données de destination. Des permissions requises pour l’accès à la base de données sont nécessaires pour les actions suivantes : écrire des données dans une base de données et créer une base de données ou une table si nécessaire:
- Dans la fenêtre Specify Table Copy or Query (Spécifier une copie de table ou une requête), choisissez l’option Copy data from one or more tables or views.
-
Choisissez les tables et les vues et sélectionnez l’option Run immediately:
Remarquez que l’utilitaire ne traitera pas une colonne de type IDENTITY différemment d’une autre colonne. Le système ne parviendra pas à insérer des données dans une table qui possède une colonne de type IDENTITY:
Si une table source a une clé de contrainte étrangère (Foreign Key Constraint), faites attention que l’utilitaire ne chargera pas les tables dans un ordre spécifique. Il est possible que la table contenant une clé étrangère soit chargée avant la table contenant la clé primaire qu’elle référence. Cela causera une défaillance au niveau de contrainte de clé étrangère. Une clé étrangère est une clé utilisée pour lier deux tables entre elles. C’est un champ dans une table qui se réfère à la clé primaire d’une autre table.
Techniques de copies en masse dans SQL Server
Utilitaire bcp
L’utilitaire bcp est un outil en ligne de commande qui permet l’export de données en masse à partir d’une base de données source vers un fichier ou l’import de données en masse à partir d’un fichier vers une base de données de destination. Par exemple, pour exporter tous les EmployeeIDs et Emails vers le fichier Emails.txt à partir de la table EmployeeEmail dans la base de données TestDV, exécutez la commande bcp suivante:
bcp TestDB.dbo.EmployeeEmail out Emails.txt –c -T
L’option -c spécifie que l’utilitaire est utilisé avec des caractères et l’option -T indique une connexion sécurisée (l’utilitaire utilise des données de connexion de l’utilisateur actuellement connecté). Si l’option -T n’est pas spécifiée, un nom d’utilisateur et un mot de passe doivent être fournis avec les options -U et -P, avec U pour utilisateur et P pour mot de passe.
La table doit exister avant l’importation. Avant que les données ne soient importées dans la table de destination, les colonnes de la table de l’instance de destination doivent correspondre aux types de données qui seront importées. Pour insérer des données à partir du fichier Emails.txt dans la table NewSchema.dbo.EmployeeEmail, il est nécessaire d’employer la commande suivante:
bcp NewSchema.dbo.EmployeeEmail in Emails.txt -T –c
L’utilitaire bcp peut seulement exporter des données vers un fichier ou importer les données à partir d’un fichier. Pour pouvoir faire appel à cet utilitaire, les opérations SELECT doivent être possibles sur la table source.
Importer des données en utilisant l’instruction BULK INSERT
Pour importer des données à partir d’un fichier spécifique vers une table, la commande BULK INSERT peut être utilisée. Tout comme l’utilitaire bcp, la table de destination doit exister et le type de ses colonnes doit correspondre avec celui des enregistrements qui seront importés.
Spécifiez la table de destination et l’emplacement du fichier à partir duquel vous souhaitez importer les données:
BULK INSERT TestRemote.dbo.EmployeeEmail FROM 'C:\Users\Lenovo\Desktop\Emails.txt'; GO
Importer les données en utilisant la fonction OPENROWSET(BULK)
La fonction OPENROWSET(BULK) se connecte sur une source de données OLE DB pour restaurer des données et permet l’accès à des données d’une source distante.
La structure de la commande à utiliser est la suivante:
INSERT … SELECT * FROM OPENROWSET(BULK…)
INSERT INTO AllEmails(Email) SELECT * FROM OPENROWSET( BULK 'C:\Users\Lenovo\Desktop\Emails.txt', SINGLE_BLOB) AS x;
La fonction OPENROWSET(BULK) fournit une alternative pour accéder aux objets d’un serveur lié et est appropriée pour la saisie unique des données depuis une source distante.
Importer et exporter des données à l’aide de l’instruction SELECT INTO
Quand la clause INTO est utilisée en combinaison avec l’instruction SELECT, cela active la création d’une nouvelle table basée sur les résultats de l’instruction SELECT. Par exemple, la copie de la table EmployeeEmail sur une même instance sur la base de données TestDB, nécessite la requête suivante:
SELECT * INTO TestDB.dbo.EmployeeEmail FROM AdventureWorks2012.HumanResources.EmployeeEmail;
SELECT INTO ne peut pas être utilisé pour créer une nouvelle table sur une instance SQL Server distante, mais cette instance peut être inclue dans l’instruction SELECT, si un serveur lié vers cette instance existe.
Si la table source contient des contraintes, des indexes ou des déclencheurs, ceux-ci ne seront pas transférés vers la nouvelle table. La colonne dans la table à peine créée n’héritera pas de la propriété IDENTITY des résultats de la requête si l’instruction SELECT contient une fonction d’agrégation, dans la clause JOIN ou dans la clause GROUP BY, de même si le colonne IDENTITY est utilisée dans une expression, plus d’une fois ou à partir d’une source de données à distante.
Exporter et importer des données en utilisant un ApexSQL Script
L’utilisation d’un ApexSQL Script, un outil de migration de base de données SQL Server, réalise un script SQL pour exporter des données, ou bien à la fois les données et la structure, à partir d’une instance source et l’exécute sur une instance de destination pour importer les données.
Pour générer les données d’une base de données avec ApexSQL Script, ouvrez la boîte de dialogue “Nouveau Projet” et connectez-vous à la base de données en question. Définissez les options d’exportation en fonction de la structure de l’instance de destination. Si vous souhaitez générer à la fois la structure et les données d’une table source, veillez à déchocher les options Script CREATE DATABASE et Script USE for a database sont décochées dans l’onglet Structure Script. Si votre table comporte des contraintes, des index ou des clés étrangères, sélectionnez l’option Script Names pour utiliser le nom de ces objets:
Sous l’onglet Data script, sélectionnez l’option Set IDENTITY_INSERT ON, pour gérer le chargement des données dans les tables qui ont des colonnes de type IDENTITY. Sous l’option Rows to script, sélectionnez l’option Script rows as INSERT:
Si la base de données de destination a une structure différente de la base de données source, sous l’onglet Schema mapping, cochez l’option Exclude schemas pour exclure les schémas des noms d’objet et cliquez sur le bouton Open:
Dans la grille des résultats (Results grid), sous la grille Data, filtrez les colonnes pour extraire les données de celle-ci:
A partir de la grille de structure (Structure grid), sélectionnez les tables à scripter et démarrer l’utilitaire Script wizard:
Dans l’assistant de script (Script wizard), sélectionnez le mode de script (Scripting mode) et cliquez sur le bouton Next:
Sauvegardez le script et exécutez-le sur la même instance ou sur une instance à distance:
Copier des données en utilisant ApexSQL Data Diff
Si la table de destination existe déjà et qu’elle a la même structure que la table source, vous pouvez utiliser ApexSQL Data Diff, un outil de comparaison de données pour SQL Server. Il s’agit d’un outil permettant de copier des données à partir d’une ou plusieurs tables vers une base de données de destination.
Démarrez un nouveau projet, connectez-vous sur les bases de données de source et de destination, et cliquez sur le bouton Compare:
Dans la grille des résultats (Results grid), sélectionnez les tables à copier et cliquer sur le bouton Synchronize:
Dans l’assistant de synchronisation Synchronization wizard, vous pouvez voir l’impact du script de synchronisation ainsi que les avertissements (Warnings) et les actions:
Cliquez sur le bouton Create script et exécutez le script:
Ressources utiles:
bcp Utility
SQL Server Import and Export Wizard
Bulk Import and Export of Data (SQL Server)
August 31, 2017
