Utiliser un fichier de format pour ignorer une colonne de table (SQL Server)

Article
06/25/2013

Cette rubrique décrit les fichiers de format. 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. 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.

Exemple de table et de fichier de données

Les exemples suivants nécessitent une table nommée myTestSkipCol dans la base de données exemple AdventureWorks2012 sous le schéma dbo. Créez cette table comme suit :

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 :

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.

Utilisation d'un fichier de format non XML

Vous pouvez modifier un fichier de format non XML pour ignorer une colonne de table. 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. Le fichier de format modifié doit mapper chaque champ existant à une colonne de table correspondante et indiquer quelle(s) colonne(s) de table ignorer. Il existe deux alternatives pour modifier un fichier de données non XML par défaut. 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.

Création d'un fichier de format non XML par défaut

Cette rubrique utilise le fichier de format non XML par défaut créé pour l'exemple de table myTestSkipCol en utilisant la commande bcp suivante :

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. Ce fichier de format s'appelle un fichier de format par défaut car il est au format généré par 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.

Remarque relative à la sécurité
Vous devrez peut-être spécifier le nom de l'instance de serveur à laquelle vous vous connectez. Vous devrez aussi peut-être spécifier le nom d'utilisateur et le mot de passe. Pour plus d'informations, consultez Utilitaire bcp.

L'illustration suivante montre les valeurs dans les exemples de fichier de format par défaut. L'illustration montre également le nom de chaque champ fichier-format.

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

[!REMARQUE]

Pour plus d'informations sur les champs fichier-format, consultez Fichiers de format non-XML (SQL Server).

Méthodes de modification d'un fichier de format non XML

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 :

La méthode recommandée consiste en une procédure de trois étapes. Commencez par supprimer les lignes de fichier-format qui correspondent à un champ manquant dans le fichier de données. Puis, réduisez la valeur « Ordre des champs du fichier hôte » de chaque ligne de fichier-format qui suit une ligne supprimée. L'objectif est les valeurs « Ordre des champs du fichier hôte » séquentielles, 1 à n, qui reflètent la position réelle de chaque champ de données dans le fichier de données. 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.

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. 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. La ligne de Col2 a été supprimée. Les autres modifications apparaissent en gras :
```
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. 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. De plus, les champs « terminateur » et « classement des colonnes » doivent avoir la valeur "" (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écessaire Les champs de format restants nécessitent leurs valeurs par défaut.

L'exemple suivant provient aussi du fichier de format par défaut pour la table myTestSkipCol. Les valeurs qui doivent être 0 ou NULL sont en gras.
```
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
```

Exemples

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.

Utilisation de 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. Dans cet exemple, le fichier de format modifié est intitulé 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 Studio :

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 XML

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. Néanmoins, vous pouvez importer toutes les colonnes d'une table hormis la dernière. 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. Vous pouvez ensuite importer en bloc les données de ce fichier dans la vue.

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 :

INSERT ...<column_list> SELECT <column_list> FROM OPENROWSET(BULK...)

Création d'un fichier de format XML par défaut

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. La commande bcp suivante crée un fichier de format XML par défaut pour la table myTestSkipCol :

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 :

<?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="\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>

[!REMARQUE]

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

Exemples

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. Pour effectuer l'importation de myTestSkipCol2.dat dans la table myTestSkipCol, les exemples font appel au fichier de format XML modifié, 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.

<?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="\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...)

L'exemple suivant utilise le fournisseur d'ensembles de lignes en bloc OPENROWSET et le fichier de format myTestSkipCol2.xml. Dans cet exemple, le fichier de données myTestSkipCol2.dat est importé en bloc dans la table myTestSkipCol. L'instruction contient une liste explicite des colonnes dans la liste de sélection et aussi dans la table cible.

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

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 vue

L'exemple suivant crée la vue v_myTestSkipCol dans la table myTestSkipCol. Cette vue ignore la deuxième colonne de la table, Col2. L'exemple utilise ensuite l'instruction BULK INSERT pour importer le fichier de données myTestSkipCol2.dat dans cette vue.

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

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