Utiliser un fichier de format pour mapper les colonnes d’une table aux champs d’un fichier de données (SQL Server)
S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
Il se peut que les champs d'un fichier de données ne soient pas dans le même ordre que les colonnes correspondantes présentes dans la table. Cet article présente les fichiers de format non XML et XML modifiés pour prendre en charge un fichier de données dont les champs sont organisés dans un ordre différent des colonnes de la table. Le fichier de format modifié permet de mapper les champs de données sur les colonnes correspondantes de la table. Pour plus d’informations, consultez Créer un fichier de format avec bcp (SQL Server).
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 d’utilitaire bcp, d’une instruction BULK INSERT ou d’INSERT ... SELECT * FROM OPENROWSET, 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 la table et le fichier de données défini plus loin dans cet article.
Exemple de table
Le script Transact-SQL suivant crée une base de données de test et une table nommée myRemap
.
CREATE DATABASE TestDatabase;
GO
USE TestDatabase;
CREATE TABLE myRemap
(
PersonID SMALLINT,
FirstName VARCHAR (25),
LastName VARCHAR (30),
Gender CHAR (1)
);
Exemple de fichier de données
Les données suivantes présentent FirstName
et LastName
dans l’ordre inverse, comme indiqué dans le tableau myRemap
. À l’aide d’un éditeur de texte tel que le Bloc-notes, créez un fichier D:\BCP\myRemap.bcp
vide et insérez les données suivantes :
1,Grosse,Anthony,M
2,Fatnowna,Alica,F
3,Rosenhain,Stella,F
Créer les fichiers de format
Pour importer en bloc des données à partir de myRemap.bcp
la myRemap
table, le fichier de format doit effectuer les actions suivantes :
- mapper le premier champ des données à la première colonne,
PersonID
; - mapper le second champ des données à la troisième colonne,
LastName
; - mapper le troisième champ des données à la deuxième colonne,
FirstName
; - mapper le quatrième champ des données à la quatrième colonne,
Gender
.
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, myRemap.fmt
basé sur le schéma de myRemap
. 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.myRemap format nul -c -f D:\BCP\myRemap.fmt -t, -T
Modification du fichier de format non-XML
Pour plus d’informations sur la terminologie, consultez Structure des fichiers de format non XML. Ouvrez D:\BCP\myRemap.fmt
dans Bloc-notes et effectuez les modifications suivantes :
- Réorganisez l’ordre des lignes de fichier de format afin que les lignes soient dans le même ordre que les données dans
myRemap.bcp
. - Assurez-vous que les valeurs d’ordre du champ de fichier hôte soient séquentielles.
- Vérifiez qu’il existe un retour chariot après la dernière ligne de fichier de format.
Comparez les modifications :
Avant le
13.0
4
1 SQLCHAR 0 7 "," 1 PersonID ""
2 SQLCHAR 0 25 "," 2 FirstName SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 30 "," 3 LastName SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 1 "\r\n" 4 Gender SQL_Latin1_General_CP1_CI_AS
Après
13.0
4
1 SQLCHAR 0 7 "," 1 PersonID ""
2 SQLCHAR 0 30 "," 3 LastName SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 25 "," 2 FirstName SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 1 "\r\n" 4 Gender SQL_Latin1_General_CP1_CI_AS
Le fichier de format modifié reflète à présent les éléments suivants :
- Le premier champ de données figurant dans
myRemap.bcp
est mappé à la première colonne ;myRemap.. PersonID
- Le second champ de données figurant dans
myRemap.bcp
est mappé à la troisième colonne ;myRemap.. LastName
- Le troisième champ de données figurant dans
myRemap.bcp
est mappé à la deuxième colonne ;myRemap.. FirstName
- Le quatrième champ de données figurant dans
myRemap.bcp
est mappé à la quatrième colonne ;myRemap.. Gender
Création d’un fichier de format XML
Veuillez consulter 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, myRemap.xml
basé sur le schéma de myRemap
. 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.myRemap format nul -c -x -f D:\BCP\myRemap.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\myRemap.xml
dans Bloc-notes et effectuez les modifications suivantes :
L’ordre dans lequel les
<FIELD>
éléments sont déclarés dans le fichier de format est l’ordre dans lequel ces champs apparaissent dans le fichier de données. Par conséquent, inversez l’ordre des<FIELD>
éléments avec des attributs d’ID 2 et 3.Vérifiez que les valeurs de l’attribut
<FIELD>
ID sont séquentielles.L’ordre des éléments de
<COLUMN>
l’élément <ROW> définit l’ordre dans lequel l’opération en bloc les envoie à la cible. Le fichier de format XML attribue à chaque élément<COLUMN>
un nom local qui n’a aucune relation avec la colonne dans la table cible d’une opération d’importation en bloc. L’ordre des<COLUMN>
éléments est indépendant de l’ordre des<FIELD>
éléments dans une <définition RECORD> . Chaque élément<COLUMN>
correspond à un élément<FIELD>
(dont l’ID est spécifié dans l’attribut SOURCE de l’élément<COLUMN>
). Par conséquent, les valeurs de<COLUMN>
SOURCE sont les seuls attributs qui nécessitent une révision. Inversez l’ordre pour les attributs 2 et 3 de<COLUMN>
SOURCE.
Comparez les modifications :
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="," MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="1" 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"/>
<COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
</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="30" 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="1" 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="2" NAME="LastName" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
</ROW>
</BCPFORMAT>
Le fichier de format modifié reflète à présent les éléments suivants :
- FIELD 1, qui correspond à COLUMN 1, est mappé à la première colonne de la table
myRemap.. PersonID
- FIELD 2, qui correspond à LA COLONNE 2, est remappé dans la troisième colonne de table,
myRemap.. LastName
- FIELD 3, qui correspond à LA COLONNE 3, est remappé à la deuxième colonne de table,
myRemap.. FirstName
- FIELD 4, qui correspond à COLUMN 4, est mappé à la quatrième colonne de la table
myRemap.. Gender
Importation de données avec un fichier de format pour mapper les colonnes d’une table aux champs d’un fichier de données
Les exemples suivants utilisent la base de données, le fichier de données et les fichiers de format créés précédemment.
Utilisation de bcp et d’un fichier de format non-XML
À l’invite de commandes, entrez la commande suivante.
bcp TestDatabase.dbo.myRemap IN D:\BCP\myRemap.bcp -f D:\BCP\myRemap.fmt -T
Utilisation de bcp et d’un fichier de format XML
À l’invite de commandes, entrez la commande suivante.
bcp TestDatabase.dbo.myRemap IN D:\BCP\myRemap.bcp -f D:\BCP\myRemap.xml -T
Utiliser BULK INSERT et un fichier de format non-XML
Exécutez le script Transact-SQL suivant.
USE TestDatabase;
GO
TRUNCATE TABLE myRemap;
BULK INSERT dbo.myRemap FROM 'D:\BCP\myRemap.bcp'
WITH (FORMATFILE = 'D:\BCP\myRemap.fmt');
GO
-- review results
SELECT *
FROM TestDatabase.dbo.myRemap;
Utiliser BULK INSERT et un fichier de format XML
Exécutez le script Transact-SQL suivant.
USE TestDatabase;
GO
TRUNCATE TABLE myRemap;
BULK INSERT dbo.myRemap FROM 'D:\BCP\myRemap.bcp'
WITH (FORMATFILE = 'D:\BCP\myRemap.xml');
GO
-- review results
SELECT *
FROM TestDatabase.dbo.myRemap;
Utiliser le fichier de format OPENROWSET et non XML
Exécutez le script Transact-SQL suivant.
USE TestDatabase;
GO
TRUNCATE TABLE myRemap;
INSERT INTO dbo.myRemap
SELECT *
FROM OPENROWSET (
BULK 'D:\BCP\myRemap.bcp',
FORMATFILE = 'D:\BCP\myRemap.fmt'
) AS t1;
GO
-- review results
SELECT *
FROM TestDatabase.dbo.myRemap;
Utiliser le fichier de format OPENROWSET et XML
Exécutez le script Transact-SQL suivant.
USE TestDatabase;
GO
TRUNCATE TABLE myRemap;
INSERT INTO dbo.myRemap
SELECT *
FROM OPENROWSET (
BULK 'D:\BCP\myRemap.bcp',
FORMATFILE = 'D:\BCP\myRemap.xml'
) AS t1;
GO
-- review results
SELECT *
FROM TestDatabase.dbo.myRemap;