Utiliser un fichier de format pour ignorer une colonne de table (SQL Server)Use a Format File to Skip a Table Column (SQL Server)

Cette rubrique décrit les fichiers de format.This topic describes format files. Vous pouvez utiliser un fichier de format pour ignorer l'importation d'une colonne de table lorsque le champ n'existe pas dans le fichier de données.You can use a format file to skip importing a table column when the field does not exist in the data file. Un fichier de données peut contenir moins de champs qu'il n'y a de colonnes dans la table uniquement si les colonnes ignorées peuvent être NULL et/ou avoir une valeur par défaut.A data file can contain fewer fields than the number of columns in the table only if the skipped columns are nullable and/or have default value.

Exemples de table et de fichier de donnéesSample Table and Data File

Les exemples suivants nécessitent une table nommée myTestSkipCol dans la base de données exemple AdventureWorks2012AdventureWorks2012 sous le schéma dbo .The following examples require a table named myTestSkipCol in the AdventureWorks2012AdventureWorks2012 sample database under the dbo schema. Créez cette table comme suit :Create this table as follows:

USE AdventureWorks2012;  
GO  
CREATE TABLE myTestSkipCol   
   (  
   Col1 smallint,  
   Col2 nvarchar(50) NULL,  
   Col3 nvarchar(50) not NULL  
   );  
GO  

Les exemples suivants utilisent un fichier de données d'exemple, myTestSkipCol2.dat, doté uniquement de deux champs alors que la table correspondante contient trois colonnes :The following examples use a sample data file, myTestSkipCol2.dat, which contains only two fields, although the corresponding table contains three columns:

1,DataForColumn3  
1,DataForColumn3  
1,DataForColumn3  

Pour importer des données en bloc depuis myTestSkipCol2.dat dans la table myTestSkipCol , le fichier de format doit mapper le premier champ de données à Col1, le deuxième champ à Col3, en ignorant Col2.To bulk import data from myTestSkipCol2.dat into the myTestSkipCol table, the format file must map the first data field to Col1, the second field to Col3, skipping Col2.

Utilisation d'un fichier de format non-XMLUsing a Non-XML Format File

Vous pouvez modifier un fichier de format non XML pour ignorer une colonne de table.You can modify a non-XML format file to skip a table column. En règle générale, cette opération consiste à faire appel à l’utilitaire bcp pour créer un fichier de format non-XML par défaut et à modifier le fichier par défaut dans un éditeur de texte.Typically, this involves using the bcp utility to create a default non-XML format file and the modifying the default file in a text editor. Le fichier de format modifié doit mapper chaque champ existant à une colonne de table correspondante et indiquer quelle(s) colonne(s) de table ignorer.The modified format file must map each existing fields to its corresponding table column and indicate which table column or columns to skip. Il existe deux alternatives pour modifier un fichier de données non XML par défaut.Two alternatives exist for modifying a default non-XML data file. Quoi qu'il en soit, elles indiquent toutes deux que le champ de données n'existe pas dans le fichier de données et qu'aucune donnée ne sera insérée dans la colonne correspondante de la table.Either alternative indicates that the data field does not exist in the data file and that no data will be inserted into the corresponding table column.

Création d'un fichier de format non XML par défautCreating a Default Non-XML Format File

Cette rubrique utilise le fichier de format non-XML par défaut créé pour l’exemple de table myTestSkipCol en faisant appel à la commande bcp suivante :This topic uses the default non-XML format file that was created for the myTestSkipCol sample table by using the following bcp command:

bcp AdventureWorks2012..myTestSkipCol format nul -f myTestSkipCol_Default.fmt -c -T  

La commande précédente crée un fichier de format non XML, myTestSkipCol_Default.fmt.The previous command creates a non-XML format file, myTestSkipCol_Default.fmt. Ce fichier de format s’appelle un fichier de format par défaut car il est au format généré par bcp.This format file is called a default format file because it is the form generated by bcp. Généralement, un fichier de format par défaut décrit une correspondance unique entre les champs données-fichier et les colonnes de table.Typically, a default format file describes a one-to-one correspondence between data-file fields and table columns.

Important

Vous devrez peut-être spécifier le nom de l'instance de serveur à laquelle vous vous connectez.You might have to specify the name of the server instance to which you are connecting. Vous devrez aussi peut-être spécifier le nom d'utilisateur et le mot de passe.Also, you might have to specify the user name and password. Pour plus d’informations, consultez bcp Utility.For more information, see bcp Utility.

L'illustration suivante montre les valeurs dans les exemples de fichier de format par défaut.The following illustration shows the values in this sample default format files. L'illustration montre également le nom de chaque champ fichier-format.The illustration also shows the name of each format-file field.

fichier de format non-XML par défaut pour myTestSkipColdefault non-XML format file for myTestSkipCol

Note

Pour plus d’informations sur les champs de fichier de format, consultez Fichiers de format non-XML (SQL Server).For more information about the format-file fields, see Non-XML Format Files (SQL Server).

Méthodes de modification d'un fichier de format non XMLMethods for Modifying a Non-XML Format File

Pour ignorer une colonne de table, modifiez le fichier de format non XML par défaut et modifiez-le à l'aide de l'une des méthodes alternatives suivantes :To skip a table column, edit the default non-XML format file and modify the file by using one of the following alternative methods:

  • La méthode recommandée consiste en une procédure de trois étapes.The preferred method involves three basic steps. Commencez par supprimer les lignes de fichier-format qui correspondent à un champ manquant dans le fichier de données.First, delete any format-file row that describes a field that is missing from the data file. Puis, réduisez la valeur « Ordre des champs du fichier hôte » de chaque ligne de fichier-format qui suit une ligne supprimée.Then, reduce the "Host file field order" value of each format-file row that follows a deleted row. L'objectif est les valeurs « Ordre des champs du fichier hôte » séquentielles, 1 à n, qui reflète la position réelle de chaque champ de données dans le fichier de données.The goal is sequential "Host file field order" values, 1 through n, that reflect the actual position of each data field in the data file. Enfin, réduisez la valeur du champ « Nombre de colonnes » pour refléter le nombre réel de champs figurant dans le fichier de données.Finally, reduce the value in the "Number of columns" field to reflect the actual number of fields in the data file.

    L'exemple suivant est basé sur le fichier de format par défaut pour la table myTestSkipCol et créé dans la section « Création d'un fichier de format non XML par défaut », plus haut dans cette rubrique.The following example is based on the default format file for the myTestSkipCol table, which is created in "Creating a Default Non-XML Format File," earlier in this topic. Ce fichier de format modifié mappe le premier champ de données à Col1, ignore Col2, et mappe le deuxième champ de données à Col3.This modified format file maps the first data field to Col1, skips Col2, and maps the second data field to Col3. La ligne de Col2 a été supprimée.The row for Col2 has been deleted.

    9.0  
    2  
    1       SQLCHAR       0       7       "\t"     1     Col1         ""  
    2       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS  
    
  • Pour ignorer une colonne de table, vous pouvez aussi modifier la définition de la ligne du fichier-format qui correspond à la colonne de table.Alternatively, to skip a table column, you can modify the definition of the format-file row that corresponds to the table column. Dans cette ligne de fichier-format, les valeurs « longueur de préfixe », « longueur des données du fichier hôte » et « ordre des colonnes du serveur » doivent être égales à 0.In this format-file row, the "prefix length," "host file data length," and "server column order" values must be set to 0. De plus, les champs « terminateur » et « classement des colonnes » doivent avoir la valeur "" (NULL).Also, the "terminator" and "column collation" fields must be set to "" (NULL).

    La valeur « nom de la colonne du serveur » nécessite une chaîne non vide même si le nom de la colonne à proprement dit n'est pas nécessaireThe "server column name" value requires a nonblank string, though the actual column name is not necessary. Les champs de format restants nécessitent leurs valeurs par défaut.The remaining format fields require their default values.

    L'exemple suivant provient aussi du fichier de format par défaut pour la table myTestSkipCol .The following example is also derived from the default format file for the myTestSkipCol table.

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

ExemplesExamples

Les exemples suivants sont aussi basés sur l'exemple de table myTestSkipCol et l'exemple de fichier de données myTestSkipCol2.dat créés dans la section « Exemple de table et de fichier de données », plus haut dans cette rubrique.The following examples are also based on the myTestSkipCol sample table and the myTestSkipCol2.dat sample data file that are created in "Sample Table and Data File," earlier in this topic.

Utilisation de BULK INSERTUsing BULK INSERT

Cet exemple décrit l'utilisation de l'un ou l'autre des fichiers de format non XML modifiés et créés dans la section « Méthodes de modification d'un fichier de format non XML », plus haut dans cette rubrique.This example works by using either of the modified non-XML format files created in "Methods for Modifying a Non-XML Format File," earlier in this topic. Dans cet exemple, le fichier de format modifié est intitulé C:\myTestSkipCol2.fmt.In this example, the modified format file is named C:\myTestSkipCol2.fmt. Pour utiliser BULK INSERT afin d'importer en bloc le fichier de données myTestSkipCol2.dat , exécutez le code suivant dans l'éditeur de requête SQL Server Management StudioSQL Server Management Studio :To use BULK INSERT to bulk import the myTestSkipCol2.dat data file, in the SQL Server Management StudioSQL Server Management Studio Query Editor, execute the following code:

USE AdventureWorks2012;  
GO  
BULK INSERT myTestSkipCol   
   FROM 'C:\myTestSkipCol2.dat'   
   WITH (FORMATFILE = 'C:\myTestSkipCol2.fmt');  
GO  
SELECT * FROM myTestSkipCol;  
GO  

Utilisation d'un fichier de format XMLUsing an XML Format File

Avec un fichier de format XML, vous ne pouvez pas ignorer une colonne lorsque vous procédez à une importation directement dans une table à l’aide d’une commande bcp ou d’une instruction BULK INSERT.With an XML format file, you cannot skip a column when you are importing directly into a table by using a bcp command or a BULK INSERT statement. Néanmoins, vous pouvez importer toutes les colonnes d'une table hormis la dernière.However, you can import into all but the last column of a table. Pour ignorer toutes les colonnes à l'exception de la dernière, vous devez créer une vue de la table cible contenant uniquement les colonnes figurant dans le fichier de données.If you have to skip any but the last column, you must create a view of the target table that contains only the columns contained in the data file. Vous pouvez ensuite importer en bloc les données de ce fichier dans la vue.Then, you can bulk import data from that file into the view.

Pour utiliser un fichier de format XML afin d'ignorer une colonne de table à l'aide de OPENROWSET(BULK...), vous devez fournir une liste explicite des colonnes dans la liste de sélection mais aussi dans la table cible, comme ci-dessous :To use an XML format file to skip a table column by using OPENROWSET(BULK...), you have to provide explicit list of columns in the select list and also in the target table, as follows:

INSERT ... SELECT FROM OPENROWSET(BULK...)INSERT ... SELECT FROM OPENROWSET(BULK...)

Création d'un fichier de format XML par défautCreating a Default XML Format File

Les exemples de fichiers de format modifiés sont basés sur l'exemple de table myTestSkipCol et de fichier de données créés dans la section « Exemple de table et de fichier de données », plus haut dans cette rubrique.The examples of modified format files are based on the myTestSkipCol sample table and data file that are created in "Sample Table and Data File," earlier in this topic. La commande bcp suivante crée un fichier de format XML par défaut pour la table myTestSkipCol :The following bcp command creates a default XML format file for the myTestSkipCol table:

bcp AdventureWorks2012..myTestSkipCol format nul -f myTestSkipCol_Default.xml -c -x -T  

Le fichier de format non XML par défaut résultant décrit une correspondance unique entre les champs données-fichier et les colonnes de table de la manière suivante :The resulting default non-XML format file describes a one-to-one correspondence between data-file fields and table columns, as follows:

\<?xml version="1.0"?>  
\<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
 <RECORD>  
  \<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="7"/>  
  \<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
  \<FIELD ID="3" 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"/>  
 </ROW>  
</BCPFORMAT>  
Note

Pour plus d’informations sur la structure des fichiers de format XML, consultez Fichiers de format XML (SQL Server).For information about the structure of XML format files, see XML Format Files (SQL Server).

ExemplesExamples

Les exemples de cette section utilisent l'exemple de table myTestSkipCol et l'exemple de fichier de données myTestSkipCol2.dat de la section « Exemple de table et de fichier de données », plus haut dans cette rubrique.The examples in this section use the myTestSkipCol sample table and the myTestSkipCol2.dat sample data file that are created in "Sample Table and Data File," earlier in this topic. Pour effectuer l'importation de myTestSkipCol2.dat dans la table myTestSkipCol , les exemples font appel au fichier de format XML modifié, myTestSkipCol2-x.xml.To import the data from myTestSkipCol2.dat into the myTestSkipCol table, the examples use the following modified XML format file, myTestSkipCol2-x.xml. Ces exemples sont basés sur le fichier de format créé dans la section « Création d'un fichier de format XML par défaut », plus haut dans cette rubrique.This is based on the format file that is created in "Creating a Default XML Format File," earlier in this topic.

\<?xml version="1.0"?>  
\<BCPFORMAT xmlns="http://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="\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="Col3" xsi:type="SQLNVARCHAR"/>  
 </ROW>  
</BCPFORMAT>  

Utilisation de OPENROWSET(BULK...)Using OPENROWSET(BULK...)

L'exemple suivant utilise le fournisseur d'ensembles de lignes en bloc OPENROWSET et le fichier de format myTestSkipCol2.xml .The following example uses the OPENROWSET bulk rowset provider and the myTestSkipCol2.xml format file. Dans cet exemple, le fichier de données myTestSkipCol2.dat est importé en bloc dans la table myTestSkipCol .The example bulk imports the myTestSkipCol2.dat data file into the myTestSkipCol table. L'instruction contient une liste explicite des colonnes dans la liste de sélection et aussi dans la table cible.The statement contains an explicit list of columns in the select list and also in the target table, as required.

Dans l'Éditeur de requête SQL Server Management StudioSQL Server Management Studio , exécutez le code suivant :In the SQL Server Management StudioSQL Server Management Studio Query Editor, execute the following code:

USE AdventureWorks2012;  
GO  
INSERT INTO myTestSkipCol  
  (Col1,Col3)  
    SELECT Col1,Col3  
      FROM  OPENROWSET(BULK  'C:\myTestSkipCol2.Dat',  
      FORMATFILE='C:\myTestSkipCol2.Xml'    
       ) as t1 ;  
GO  

Utilisation de BULK IMPORT dans une vueUsing BULK IMPORT on a View

L'exemple suivant crée la vue v_myTestSkipCol dans la table myTestSkipCol .The following example creates the v_myTestSkipCol on the myTestSkipCol table. Cette vue ignore la deuxième colonne de la table, Col2.This view skips the second table column, Col2. L'exemple utilise ensuite l'instruction BULK INSERT pour importer le fichier de données myTestSkipCol2.dat dans cette vue.The example then uses BULK INSERT to import the myTestSkipCol2.dat data file into this view.

Dans l'Éditeur de requête SQL Server Management StudioSQL Server Management Studio , exécutez le code suivant :In the SQL Server Management StudioSQL Server Management Studio Query Editor, execute the following code:

CREATE VIEW v_myTestSkipCol AS  
    SELECT Col1,Col3  
    FROM myTestSkipCol;  
GO  

USE AdventureWorks2012;  
GO  
BULK INSERT v_myTestSkipCol  
FROM 'C:\myTestSkipCol2.dat'  
WITH (FORMATFILE='C:\myTestSkipCol2.xml');  
GO  

Voir aussiSee Also

bcp Utility bcp Utility
BULK INSERT (Transact-SQL) BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
Utiliser un fichier de format pour ignorer un champ de données (SQL Server) Use a Format File to Skip a Data Field (SQL Server)
Utiliser un fichier de format pour mapper les colonnes d’une table aux champs d’un fichier de données (SQL Server) Use a Format File to Map Table Columns to Data-File Fields (SQL Server)
Utiliser un fichier de format pour importer des données en bloc (SQL Server) Use a Format File to Bulk Import Data (SQL Server)