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. 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. Veuillez consulter Créer un fichier de format (SQL Server) pour plus d’informations.
Notes
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 utility, 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).
Notes
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 fichiers de format modifiés pris en exemple dans cette rubrique sont fondés sur la table et le fichier de données définis ci-dessous.
Exemple de table
Le script ci-dessous crée une base de données de test et une table nommée myTestSkipField. Exécutez l’instruction Transact-SQL suivante 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
Pour plus d’informations, consultez Fichiers de format non XML (SQL Server). 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
Modifier le fichier de format non XML
Passez en revue la structure des fichiers de format non XML pour la terminologie. Ouvrez D:\BCP\myTestSkipField.fmt dans Bloc-notes et effectuez les modifications suivantes :
- Copiez l’intégralité de la ligne format-file pour
FirstNameet collez-la directement aprèsFirstNamesur la ligne suivante. - Incrémentez de 1 la valeur d’ordre des champs du fichier hôte pour la nouvelle ligne et toutes les lignes suivantes.
- Augmentez la valeur du nombre de colonnes pour refléter le nombre réel de champs figurant dans le fichier de données.
- Modifiez l’ordre des colonnes du serveur en les faisant passer de
2à0pour la deuxième ligne format-file.
Comparez les modifications apportées :
Avant
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.bcpest mappé à la première colonne ;myTestSkipField.. PersonID - Le deuxième champ de données dans
myTestSkipField.bcpn’est mappé à aucune colonne. - Le troisième champ de données figurant dans
myTestSkipField.bcpest mappé à la deuxième colonne ;myTestSkipField.. FirstName - Le quatrième champ de données figurant dans
myTestSkipField.bcpest mappé à la troisième colonne ;myTestSkipField.. LastName
Créer un fichier de format XML
Pour plus d’informations, consultez Fichiers de format XML (SQL Server). La commande suivante utilise l’ utilitaire bcp pour créer un fichier de format xml myTestSkipField.xmlbasé 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. 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
Modifier le fichier de format XML
Passez en revue la 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 :
- Copiez la totalité du deuxième champ et collez-la directement après le deuxième champ sur la ligne suivante.
- Incrémentez la valeur « FIELD ID » de 1 pour le nouveau champ et pour chaque champ suivant.
- Incrémentez la valeur de « COLUMN SOURCE » de 1 pour
FirstName, etLastNameafin de refléter le mappage modifié.
Comparez les modifications apportées :
Avant
<?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 la table.
myTestSkipField.. PersonID - FIELD 2 ne correspond à aucune COLUMN et n’est, par conséquent, mappé à aucune colonne de la table.
- FIELD 3 qui correspond à COLUMN 3 est mappé à la seconde colonne de la table.
myTestSkipField.. FirstName - FIELD 4 qui correspond à COLUMN 4 est mappé à la troisième colonne de la table.
myTestSkipField.. LastName
Importer des données avec un fichier de format pour ignorer un champ de données
Les exemples ci-dessous utilisent la base de données, le fichier de données et les fichiers de format créés ci-dessus.
Utiliser un fichier de format bcp et 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 le fichier de format bcp et XML
À 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 et un fichier de format non XML
Exécutez l’instruction Transact-SQL suivante 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 et le fichier de format XML
Exécutez l’instruction Transact-SQL suivante 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 un fichier de format OPENROWSET(BULK...) et non XML
Exécutez l’instruction Transact-SQL suivante 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 le fichier de format OPENROWSET(BULK...) et XML
Exécutez l’instruction Transact-SQL suivante 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;
Étapes suivantes
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour