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

Article
06/13/2017

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 utiliser un fichier de format non XML ou XML pour importer des données en bloc lors de l’utilisation d’une commande bcp ou d’un BULK INSERT ou INSERT ... SELECT * FROM OPENROWSET(BULK...) Commande Transact-SQL.

Important

Pour qu'un fichier de format fonctionne avec un fichier de données de caractères Unicode, tous les champs d'entrée doivent être des chaînes de texte Unicode (autrement dit, des chaînes Unicode de taille fixe ou terminées par un caractère).

Notes

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 = 'chemin_fichier_format'
INSERT ... SELECT * FROM OPENROWSET(BULK...)	FORMATFILE = 'chemin_fichier_format'
bcp ... Dans	-fformat_file

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

Notes

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 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 comment utiliser des fichiers de format pour importer des données en bloc à l’aide de la commande bcp et des commandes BULK INSERT et INSERT ... INSTRUCTIONS 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

Les exemples nécessitent la création d’une table nommée table myTestFormatFiles dans l’exemple de base de données AdventureWorks2012 sous le schéma dbo . Pour créer cette table, dans SQL Server Management Studio Éditeur de requête, exécutez :

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, à l’invite de commandes Microsoft Windows, entrez :

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 utilise bcp pour importer en bloc des données à partir du myTestFormatFiles-c.Dat fichier de données dans HumanResources.myTestFormatFiles la table de 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

Notes

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

Utilisation de BULK INSERT

L’exemple suivant utilise BULK INSERT pour importer en bloc des données à partir du myTestFormatFiles-c.Dat fichier de données dans HumanResources.myTestFormatFiles la table de 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 SQL Server Management Studio Éditeur de requête, 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

Notes

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 SQL Server Management Studio Éditeur de requête, 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 :