• Article

Utilisation d'un fichier de format pour importer des données en bloc

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. Dans MicrosoftSQL Server 2005 et versions ultérieures, vous pouvez opter pour un fichier de format XML ou non pour importer des données en bloc lorsque vous utilisez une commande bcp ou une commande 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 Description des fichiers de format non XML et Description des fichiers de format XML.

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 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 ... 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 exigent de créer une table nommée myTestFormatFiles dans la base de données exemple AdventureWorks sous le schéma dbo. Pour créer cette table, dans l'Éditeur de requête SQL Server Management Studio, exécutez :

USE AdventureWorks;
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

Les exemples suivants ont 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 AdventureWorks..MyTestFormatFiles format nul -c -t, -f myTestFormatFiles.Fmt -T

Pour plus d'informations sur la création d'un fichier de format, consultez Création d'un fichier de format.

Exemple de fichier de format XML

Les exemples suivants ont recours à la commande bcp pour créer 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 AdventureWorks..MyTestFormatFiles format nul -c -t, -x -f myTestFormatFiles.Xml -T

Utilisation de la commande bcp

Dans l'exemple suivant, la commande bcp 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.

À l'invite de commandes Windows, entrez :

bcp AdventureWorks..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 AdventureWorks. 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 AdventureWorks;
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 AdventureWorks;
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 :

DROP TABLE myTestFormatFiles

[!REMARQUE]

Pour plus d'informations sur la clause OPENROWSET BULK, consultez OPENROWSET (Transact-SQL).