BULK INSERT Format File

Dernièrement, je suis tombé sur une application qui alimentait des milions de lignes de données dans une table de façon unitaire. Pour améliorer les performances d’insertion, j’ai du mettre en place une procédure qui BULK INSERT un fichier dans une table SQL Server. J’ai décidé d’utiliser un fichier de format (BCP Format File).
Ce fichier de format contient la structure de la table et du fichier.

Génération du fichier de format (Format File)

Pour générer un fichier de format, j’ai utilisé l’utilitaire BCP. Cet utilitaire bien connu des DBA permet d’exporter des données d’une table vers un fichier ou de faire le chemin inverse. Il permet également de générer des fichiers de format qui seront utilisés lors d’un BULK INSERT.

Pour les besoins de cet article, j’ai créé la table testimport dans ma base TEST. Elle contiendra les données insérées.

CREATE TABLE [dbo].[testimport](
id int NOT NULL
, name nvarchar(50)
, category smallint
, comments nvarchar(500)
, width decimal (18,2)
, height decimal (18,2)
CONSTRAINT [PK_testimport] PRIMARY KEY CLUSTERED
(
id ASC
) ON [PRIMARY]
) ON [PRIMARY]

Ensuite, en ligne de commande, je peux générer un fichier de format :

bcp « TEST.dbo.testimport » format -S »localhost » -T -f « C:\temp\testimport_format.xml » -c -x -t »; » -r »\n »

fichier_format_code

Explication :
bcp : commande d’appel de l’utilitaire. S’il n’est pas reconnu, vérifiez qu’il soit installé et votre PATH.
« TEST.dbo.testimport » : la table utilisée pour générer le formatfile
format : c’est cet argument qui permet de générer un fichier de format
-S « localhost » : le nom de mon instance locale
-T : authentification Windows. On peut également utiliser l’authentification SQL
-f « C:\temp\testimport_format.xml » : le nom de mon fichier de format qui sera créé.
-c : type de données char. -w pour de l’unicode
-x : format XML pour le format file
-t « ; » : le séparateur de colonne choisi
-r « \n » : le séparateur de ligne choisi

Vous pouvez retrouver toutes les options sur le site MSDN

Et voici le résultat :

Format file généré

Format file généré

BULK INSERT

J’ai donc ce fichier de 10 000 lignes à insérer dans ma table. Et grâce à BULK INSERT, je peux importer par lot mes données en T-SQL.

Le fichier à insérer

Le fichier à insérer

Et voici la requête qui permet de BULK INSERT

BULK INSERT TEST.dbo.testimport FROM 'C:\temp\testimport.dat'
WITH (
   BATCHSIZE =1000, MAXERRORS = 1
   ,FORMATFILE='C:\temp\testimport_format.xml');