Partager via

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

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Le nombre de champs contenus dans un fichier de données peut être supérieur au nombre de colonnes dans la table. Cet article décrit la modification des fichiers de format non XML et XML pour prendre en charge un fichier de données avec plus de champs en mappant les colonnes de table aux champs de données correspondants et en ignorant les champs supplémentaires.

Pour plus d’informations, consultez Créer un fichier de format avec bcp (SQL Server).

Remarque

Un fichier de format non XML ou XML peut être utilisé pour importer en bloc un fichier de données dans la table à l’aide d’une commande de l’utilitaire bcp , d’une instruction BULK INSERT (Transact-SQL) ou d’INSERT ... SELECT * FROM OPENROWSET BULK (Transact-SQL) instruction. Pour plus d’informations, consultez Utiliser un fichier de format pour importer en bloc des données (SQL Server).

Remarque

Cette syntaxe, y compris l’insertion en bloc, n’est pas prise en charge dans Azure Synapse Analytics. Dans Azure Synapse Analytics et d’autres intégrations de plateforme de base de données cloud, effectuez le déplacement des données via l’instruction COPY dans Azure Data Factory ou à l’aide d’instructions T-SQL telles que COPY INTO et PolyBase.

Exemples de conditions de test

Les exemples de fichiers de format modifiés de cet article sont basés sur l’exemple de tableau myTestSkipField et de fichier de D:\BCP\myTestSkipField.bcpdonnées . Remplacez l’emplacement du fichier local dans l’exemple de code par un emplacement de fichier sur votre ordinateur.

Exemple de table

Le script crée une base de données de test et une table nommée myTestSkipField. Exécutez l’instruction Transact-SQL suivant dans Microsoft SQL Server Management Studio (SSMS) :

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE myTestSkipField
   (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30)
   );

Exemple de fichier de données

Créez un fichier vide D:\BCP\myTestSkipField.bcp et insérez les données suivantes :

1,SkipMe,Anthony,Grosse
2,SkipMe,Alica,Fatnowna
3,SkipMe,Stella,Rosenhain

Créer les fichiers de format

Pour effectuer l’importation en bloc de données de myTestSkipField.bcp 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.

La méthode la plus simple pour créer le fichier de format consiste à utiliser bcp utility. Tout d’abord, créez un fichier de format de base à partir de la table existante. Ensuite, modifiez le fichier de format de base afin qu’il reflète le fichier de données réel.

Créer un fichier de format non XML

Consultez Utiliser des fichiers de format non XML (SQL Server) pour obtenir des informations détaillées. La commande suivante utilise l’utilitaire bcp pour générer un fichier de format non xml myTestSkipField.fmtbasé sur le schéma de myTestSkipField. En outre, le qualificateur c est utilisé pour sépcifier les données de caractère, t, est utilisé pour spécifier une virgule comme délimiteur de champ, et T est utilisé pour spécifier une connexion approuvée à l’aide de la sécurité intégrée. À partir d'une invite de commandes, entrez la commande suivante :

bcp TestDatabase.dbo.myTestSkipField format nul -c -f D:\BCP\myTestSkipField.fmt -t, -T

Modification du fichier de format non-XML

Consultez Structure des fichiers de format non XML pour la terminologie. Ouvrez D:\BCP\myTestSkipField.fmt dans Bloc-notes et effectuez les modifications suivantes :

  1. Copiez l’intégralité de la ligne format-file pour FirstName et collez-la directement après FirstName sur la ligne suivante.
  2. Incrémentez de 1 la valeur d’ordre des champs du fichier hôte pour la nouvelle ligne et toutes les lignes suivantes.
  3. Augmentez la valeur du nombre de colonnes pour refléter le nombre réel de champs figurant dans le fichier de données.
  4. Modifiez l’ordre des colonnes du serveur en les faisant passer de 2 à 0 pour la deuxième ligne format-file.

Comparez les modifications apportées :

Avant le

13.0
3
1       SQLCHAR    0       7       ","      1     PersonID        ""
2       SQLCHAR    0       25      ","      2     FirstName    SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR    0       30      "\r\n"   3     LastName     SQL_Latin1_General_CP1_CI_AS

Après

13.0
4
1       SQLCHAR    0       7       ","      1     PersonID     ""
2       SQLCHAR    0       25      ","      0     FirstName    SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR    0       25      ","      2     FirstName    SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR    0       50      "\r\n"   3     LastName     SQL_Latin1_General_CP1_CI_AS

Le fichier de format modifié reflète à présent les éléments suivants :

  • 4 champs de données
  • Le premier champ de données figurant dans myTestSkipField.bcp est mappé à la première colonne ; myTestSkipField.. PersonID
  • Le deuxième champ de données dans myTestSkipField.bcp n’est mappé à aucune colonne.
  • Le troisième champ de données figurant dans myTestSkipField.bcp est mappé à la deuxième colonne ; myTestSkipField.. FirstName
  • Le quatrième champ de données figurant dans myTestSkipField.bcp est mappé à la troisième colonne ; myTestSkipField.. LastName

Création d’un fichier de format XML

Passez en revue les fichiers de format XML (SQL Server) pour obtenir des informations détaillées. La commande suivante utilise l’ utilitaire bcp pour créer un fichier de format xml myTestSkipField.xmlbasé sur le schéma de myTestSkipField.

  • Le qualificateur c est utilisé pour spécifier des données de caractère
  • t, est utilisé pour spécifier une virgule comme marque de fin de champ
  • T est utilisé pour spécifier une connexion approuvée à l’aide de la sécurité intégrée.
  • Le qualificateur x doit être utilisé pour générer un fichier de format XML.

À partir d'une invite de commandes, entrez la commande suivante :

bcp TestDatabase.dbo.myTestSkipField format nul -c -x -f D:\BCP\myTestSkipField.xml -t, -T

Modification du fichier de format XML

Consultez Syntaxe de schéma pour les fichiers de format XML pour la terminologie. Ouvrez D:\BCP\myTestSkipField.xml dans Bloc-notes et effectuez les modifications suivantes :

  1. Copiez la totalité du deuxième champ et collez-la directement après le deuxième champ sur la ligne suivante.
  2. Augmentez la FIELD ID valeur de 1 pour le nouveau FIELD et pour chaque autre FIELD.
  3. Augmentez la COLUMN SOURCE valeur de 1 pour FirstNameet LastName reflètez le mappage révisé.

Comparez les modifications apportées :

Avant le

<?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="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

Après

<?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="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="3" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="LastName" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

Le fichier de format modifié reflète à présent les éléments suivants :

  • 4 champs de données
  • FIELD 1 qui correspond à COLUMN 1 est mappé à la première colonne de table, myTestSkipField.. PersonID
  • FIELD 2 ne correspond à aucun COLUMN et n’est donc pas mappé à une colonne de table.
  • FIELD 3 qui correspond à COLUMN 3 est mappé à la deuxième colonne de table, myTestSkipField.. FirstName
  • FIELD 4 qui correspond à COLUMN 4 est mappé à la troisième colonne de table, myTestSkipField.. LastName

Importation de données avec un fichier de format pour ignorer un champ de données

Les exemples utilisent l’exemple de base de données, de fichier de données et de fichiers de format créés dans cet article.

Utilisation de bcp et d’un fichier de format non-XML

À partir d'une invite de commandes, entrez la commande suivante :

bcp TestDatabase.dbo.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.fmt -T

Utiliser des fichiers de format bcp et XML (SQL Server)

À partir d'une invite de commandes, entrez la commande suivante :

bcp TestDatabase.dbo.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.xml -T

Utiliser BULK INSERT (Transact-SQL) et un fichier de format non XML

Exécutez l’instruction Transact-SQL suivant dans Microsoft SQL Server Management Studio (SSMS) :

USE TestDatabase;  
GO

TRUNCATE TABLE myTestSkipField;
BULK INSERT dbo.myTestSkipField  
   FROM 'D:\BCP\myTestSkipField.bcp'  
   WITH (FORMATFILE = 'D:\BCP\myTestSkipField.fmt');  
GO

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

Utiliser BULK INSERT (Transact-SQL) et des fichiers de format XML (SQL Server)

Exécutez l’instruction Transact-SQL suivant dans Microsoft SQL Server Management Studio (SSMS) :

USE TestDatabase;  
GO

TRUNCATE TABLE myTestSkipField;
BULK INSERT dbo.myTestSkipField  
   FROM 'D:\BCP\myTestSkipField.bcp'  
   WITH (FORMATFILE = 'D:\BCP\myTestSkipField.xml');  
GO

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

Utiliser OPENROWSET BULK (Transact-SQL) et un fichier de format non XML

Exécutez l’instruction Transact-SQL suivant dans Microsoft SQL Server Management Studio (SSMS) :

USE TestDatabase;
GO

TRUNCATE TABLE myTestSkipField;
INSERT INTO dbo.myTestSkipField
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myTestSkipField.bcp',
        FORMATFILE = 'D:\BCP\myTestSkipField.fmt'
        ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

Utiliser OPENROWSET BULK (Transact-SQL) et des fichiers de format XML (SQL Server)

Exécutez l’instruction Transact-SQL suivant dans Microsoft SQL Server Management Studio (SSMS) :

USE TestDatabase;  
GO

TRUNCATE TABLE myTestSkipField;
INSERT INTO dbo.myTestSkipField  
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myTestSkipField.bcp',
        FORMATFILE = 'D:\BCP\myTestSkipField.xml'  
       ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

Contenu connexe

  • Last updated on