Utiliser un fichier de format pour importer des données en bloc (SQL Server)

Article
06/25/2013

Cette rubrique illustre l'utilisation d'un fichier de format dans les importations en bloc. Le fichier de format met en relation les champs du fichier de données avec les colonnes de la table. Vous pouvez opter pour un fichier de format XML ou non-XML pour importer des données en bloc lorsque vous utilisez une commande bcp ou une instruction BULK INSERT ou INSERT... SELECT * FROM OPENROWSET(BULK...)Transact-SQL.

Important

Pour qu'un fichier de format puisse fonctionner avec un fichier de données à caractères Unicode, tous les champs d'entrée doivent être des chaînes de texte Unicode (c'est-à-dire des chaînes Unicode de taille fixe ou se terminant par des caractères).

[!REMARQUE]

Si vous n'êtes pas familiarisé avec les fichiers de format, consultez Fichiers de format non-XML (SQL Server) et Fichiers de format XML (SQL Server).

Options des fichiers de format pour les commandes d'importation en bloc

Le tableau ci-dessous récapitule l'option de fichier de format de chaque commande d'importation en bloc.

Commande de chargement en bloc	Utilisation de l'option de fichier de format
BULK INSERT	FORMATFILE = 'format_file_path'
INSERT ... SELECT * FROM OPENROWSET(BULK...)	FORMATFILE = 'format_file_path'
bcp … in	-fformat_file

Pour plus d'informations, consultez Utilitaire bcp, BULK INSERT (Transact-SQL) ou OPENROWSET (Transact-SQL).

[!REMARQUE]

Pour exporter ou importer en bloc des données SQLXML, utilisez l'un des types de données ci-dessous dans votre fichier de format : SQLCHAR ou SQLVARYCHAR (les données sont envoyées dans la page de codes du client ou dans la page de codes inhérente au classement) ; SQLNCHAR ou SQLNVARCHAR (les données sont envoyées au format Unicode) ; SQLBINARY ou SQLVARYBIN (les données sont envoyées sans être converties).

Exemples

Les exemples de cette section illustrent l'utilisation des fichiers de format pour importer des données en bloc au moyen de la commande bcp et des instructions BULK INSERT et INSERT ... SELECT * FROM OPENROWSET(BULK...). Avant de pouvoir exécuter un des exemples d'importation en bloc, vous devez créer un exemple de table, de fichier de données et de fichier de format.

Exemple de table

Une table nommée myTestFormatFiles doit être créée dans l'exemple de base de données AdventureWorks2012 sous le schéma dbo. Pour créer cette table, dans l'éditeur de requêtes SQL Server Management Studio, exécutez le code suivant :

USE AdventureWorks2012;
GO
CREATE TABLE myTestFormatFiles (
   Col1 smallint,
   Col2 nvarchar(50),
   Col3 nvarchar(50),
   Col4 nvarchar(50)
   );
GO

Fichier de données d'exemple

Les exemples utilisent un fichier de données d'exemple, myTestFormatFiles-c.Dat, qui contient les enregistrements suivants. Pour créer le fichier de données, dans l'invite de commandes Microsoft Windows, tapez :

10,Field2,Field3,Field4
15,Field2,Field3,Field4
46,Field2,Field3,Field4
58,Field2,Field3,Field4

Exemple de fichiers de format

Certains exemples de cette section utilisent un fichier de format XML, myTestFormatFiles-f-x-c.Xml, d'autres exemples un fichier de format non XML. Ces deux types de fichiers utilisent des formats de données de type caractère et une marque de fin de champ non définie par défaut (,).

Exemple de fichier de format non XML

L'exemple suivant a recours à la commande bcp pour générer un fichier de format XML à partir de la table myTestFormatFiles. Le fichier myTestFormatFiles.Fmt contient les informations suivantes :

9.0
4
1       SQLCHAR       0       7       ","      1     Col1         ""
2       SQLCHAR       0       100     ","      2     Col2         SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       100     ","      3     Col3         SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       100     "\r\n"   4     Col4         SQL_Latin1_General_CP1_CI_AS

Pour utiliser la commande bcp avec l'option format pour créer ce fichier de format, dans l'invite de commandes Windows, tapez :

bcp AdventureWorks2012..MyTestFormatFiles format nul -c -t, -f myTestFormatFiles.Fmt -T

Pour plus d'informations sur la création d'un fichier de format, consultez Créer un fichier de format (SQL Server).

Exemple de fichier de format XML

L'exemple suivant a recours à la commande bcp pour générer un fichier de format XML à partir de la table myTestFormatFiles. Le fichier myTestFormatFiles.Xml contient les informations suivantes :

<?xml version="1.0"?>
<BCPFORMAT xmlns="https://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

Pour utiliser la commande bcp avec l'option format pour créer ce fichier de format, dans l'invite de commandes Windows, tapez :

bcp AdventureWorks2012..MyTestFormatFiles format nul -c -t, -x -f myTestFormatFiles.Xml -T

Utilisation de la commande bcp

L'exemple suivant a recours à la commande bcp pour importer des données en bloc à partir du fichier de données myTestFormatFiles-c.Dat dans la table HumanResources.myTestFormatFiles dans l'exemple de base de données. Cet exemple utilise le fichier de format XML MyTestFormatFiles.Xml. Il supprime toutes les lignes existantes de la table avant d'importer le fichier de données.

À l'invite de commandes Windows, entrez :

bcp AdventureWorks2012..myTestFormatFiles in C:\myTestFormatFiles-c.Dat -f C:\myTestFormatFiles.Xml -T

[!REMARQUE]

Pour plus d'informations sur cette commande, consultez Utilitaire bcp.

Utilisation de BULK INSERT

Dans l'exemple suivant, l'instruction BULK INSERT est utilisée pour importer des données en bloc à partir du fichier de données myTestFormatFiles-c.Dat dans la table HumanResources.myTestFormatFiles dans l'exemple de base de données AdventureWorks2012. Cet exemple utilise le fichier de format non XML MyTestFormatFiles.Fmt. Il supprime toutes les lignes existantes de la table avant d'importer le fichier de données.

Dans l'Éditeur de requête SQL Server Management Studio, exécutez :

USE AdventureWorks2012;
GO
DELETE myTestFormatFiles;
GO
BULK INSERT myTestFormatFiles 
   FROM 'C:\myTestFormatFiles-c.Dat' 
   WITH (FORMATFILE = 'C:\myTestFormatFiles.Fmt');
GO
SELECT * FROM myTestFormatFiles;
GO

[!REMARQUE]

Pour plus d'informations sur cette instruction, consultez BULK INSERT (Transact-SQL).

Utilisation du fournisseur d'ensembles de lignes en bloc OPENROWSET

Dans l'exemple suivant, la commande INSERT ... SELECT * FROM OPENROWSET(BULK...) est utilisée pour importer des données en bloc à partir du fichier de données myTestFormatFiles-c.Dat dans la table HumanResources.myTestFormatFiles dans l'exemple de base de données AdventureWorks. Cet exemple utilise le fichier de format XML MyTestFormatFiles.Xml. Il supprime toutes les lignes existantes de la table avant d'importer le fichier de données.

Dans l'Éditeur de requête SQL Server Management Studio, exécutez :

USE AdventureWorks2012;
DELETE myTestFormatFiles;
GO
INSERT INTO myTestFormatFiles
    SELECT *
      FROM  OPENROWSET(BULK  'C:\myTestFormatFiles-c.Dat',
      FORMATFILE='C:\myTestFormatFiles.Xml'     
      ) as t1 ;
GO
SELECT * FROM myTestFormatFiles;
GO

Lorsque vous avez terminé d'utiliser la table d'exemple, vous pouvez la supprimer au moyen de l'instruction suivante :