Utiliser un fichier de format pour ignorer un champ de données (SQL Server)

Article
06/25/2013

Le nombre de champs contenus dans un fichier de données peut être supérieur au nombre de colonnes dans la table. Cette rubrique explique comment modifier les fichiers de format non-XML et XML en mappant les colonnes de la table avec les champs de données correspondants et en ignorant les champs supplémentaires afin de prendre en charge un fichier de données contenant davantage de champs.

[!REMARQUE]

Vous pouvez utiliser un fichier de format non-XML ou XML pour importer en bloc un fichier de données dans la table à l'aide d'une commande bcp, d'une instruction BULK INSERT ou d'une instruction INSERT ... SELECT * FROM OPENROWSET(BULK...). Pour plus d'informations, consultez Utiliser un fichier de format pour importer des données en bloc (SQL Server).

Exemples de fichier de données et de table

Dans cette rubrique, les exemples de fichiers de format modifiés sont basés sur la table et sur le fichier de données suivants.

Table d'exemple

Une table nommée myTestSkipField 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 Microsoft SQL Server Management Studio, exécutez le code suivant :

USE AdventureWorks2012;
GO
CREATE TABLE myTestSkipField 
   (
   PersonID smallint,
   FirstName nvarchar(50) ,
   LastName nvarchar(50) 
   );
GO

Fichier de données d'exemple

Le fichier de données, myTestSkipField-c.dat, contient les enregistrements suivants :

1,Skipme,DataField3,DataField4
1,Skipme,DataField3,DataField4
1,Skipme,DataField3,DataField4

Pour effectuer l'importation en bloc de données de myTestSkipField-c.dat dans la table myTestSkipField, le fichier de format doit effectuer les tâches suivantes :

mapper le premier champ des données à la première colonne, PersonID ;
ignorer le deuxième champ des données ;
mapper le troisième champ des données à la deuxième colonne, FirstName ;
mapper le quatrième champ des données à la troisième colonne, LastName.

Fichier de format non-XML pour davantage de champs de données

Le fichier de format suivant, myTestSkipField.fmt, mappe les champs de myTestSkipField-c.dat aux colonnes de la table myTestSkipField. Le fichier de format utilise un format de données de caractères. Pour ignorer le mappage d'une colonne, vous devez attribuer à son ordre de colonne la valeur 0, à l'image de la colonne ExtraField dans le fichier de format.

Le fichier de format myTestSkipField.fmt contient les informations suivantes :

9.0
4
1       SQLCHAR       0       7       ","      1     PersonID               ""
2       SQLCHAR       0       100       ","    0     ExtraField             SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       100     ","      2     FirstName              SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       100     "\r\n"   3     LastName               SQL_Latin1_General_CP1_CI_AS

[!REMARQUE]

Pour plus d'informations sur la syntaxe des fichiers de format non-XML, consultez Fichiers de format non-XML (SQL Server).

Exemples

Cet exemple fait appel à INSERT ... SELECT * FROM OPENROWSET(BULK...) et utilise le fichier de format myTestSkipField.fmt. Dans cet exemple, le fichier de données myTestSkipField-c.dat est importé en bloc dans la table myTestSkipField. Pour créer l'exemple de fichier de données et de table, consultez la section « Exemples de fichier de données et de table », plus haut dans cette rubrique.

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

USE AdventureWorks2012;
GO
INSERT INTO myTestSkipField 
   SELECT *
      FROM  OPENROWSET(BULK  'C:\myTestSkipField-c.dat',
      FORMATFILE='C:\myTestSkipField.fmt'  
       ) AS t1;
GO

Fichier de format XML pour davantage de champs de données

Le fichier de format présenté dans cet exemple est basé sur un autre fichier de format, myTestSkipField.xml, qui utilise un format de données de caractères et dont le nombre et l'ordre des champs correspondent exactement à ceux des colonnes de la table myTestSkipField. Pour visualiser le contenu de ce fichier de format, consultez Créer un fichier de format (SQL Server).

Le fichier de format suivant, myTestSkipField.xml, mappe les champs de myTestSkipField-c.dat aux colonnes de la table myTestSkipField. Le fichier de format utilise un format de données de caractères.

Le fichier de format myTestSkipField.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="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="3" NAME="FirstName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="LastName" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

Exemples

Cet exemple fait appel à INSERT ... SELECT * FROM OPENROWSET(BULK...) et utilise le fichier de format myTestSkipField.Xml. Dans cet exemple, le fichier de données myTestSkipField-c.dat est importé en bloc dans la table myTestSkipField. Pour créer l'exemple de fichier de données et de table, consultez la section « Exemples de fichier de données et de table », plus haut dans cette rubrique.

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

USE AdventureWorks2012;
GO
INSERT INTO myTestSkipField 
  SELECT *
      FROM  OPENROWSET(BULK  'C:\myTestSkipField-c.dat',
      FORMATFILE='C:\myTestSkipField.xml'  
       ) AS t1;
GO

[!REMARQUE]

Pour obtenir davantage d'informations sur la syntaxe du schéma XML et des exemples supplémentaires de fichiers de format XML, consultez Fichiers de format XML (SQL Server).