Fichiers de format pour importer ou exporter des données (SQL Server)

  • Article

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAnalytics Platform System (PDW)

Lorsque vous importez en bloc des données dans une table SQL Server ou exportez en bloc des données depuis une table, utilisez un fichier de format pour stocker toutes les informations de format nécessaires à l’exportation ou l’importation en bloc des données. Cela inclut les informations de format pour chaque champ dans un fichier de données relatif à cette table.

SQL Server prend en charge deux types de fichiers de format : XML et non XML. Les fichiers de format XML et non-XML contiennent la description de chacun des champs d'un fichier de données, et les fichiers de format XML contiennent également des descriptions des colonnes de table correspondantes. En règle générale, les fichiers de format XML et non-XML sont interchangeables. Toutefois, nous recommandons d'utiliser la syntaxe XML pour les nouveaux fichiers de format, car elle offre plusieurs avantages par rapport aux fichiers de format non-XML. Pour plus d’informations, consultez Fichiers de format XML (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.

Avantages des fichiers de format

Les fichiers de format fournissent un système souple d'écriture de fichiers de données, nécessitant peu ou aucune édition pour se conformer aux autres formats de données, ou de lecture de fichiers de données provenant d'autres logiciels.

Vous pouvez importer les données en bloc sans avoir à ajouter ou supprimer des données inutiles ou à réorganiser les données du fichier de données. Les fichiers de format peuvent être utiles lorsqu'il existe des différences entre les champs du fichier de données et les colonnes dans la table.

Exemples de fichiers de format

Les exemples suivants illustrent la structure d'un fichier de format XML et non XML. Ces fichiers de format correspondent à la table HumanResources.myTeam dans l'exemple de base de données AdventureWorks2022 . Cette table contient quatre colonnes : EmployeeID, Name, Titleet ModifiedDate.

Remarque

Pour plus d’informations sur cette table et la manière de la créer, consultez Exemple de table HumanResources.myTeam (SQL Server).

A. Utilisation d’un fichier de format non-XML

Le fichier de format non XML suivant utilise le format de données natif SQL Server de la table HumanResources.myTeam. Ce fichier de format a été créé à l'aide de la commande bcp suivante.

bcp AdventureWorks2022.HumanResources.myTeam format nul -f myTeam.Fmt -n -T

La commande bcp utilise par défaut une instance locale par défaut de SQL Server avec l’authentification Windows. Vous pouvez spécifier d’autres informations d’instance et de connexion selon vos besoins. Pour plus d’informations, consultez Utilitaire bcp. Par exemple, pour spécifier une instance nommée de serveur distant avec l’authentification Windows, utilisez :

bcp AdventureWorks2022.HumanResources.myTeam format nul -f myTeam.Fmt -n -T -S servername/instancename

Le contenu de ce fichier de format est le suivant, en commençant par le numéro de version principale de SQL Server et les informations de métadonnées de table.

14.0
4
1       SQLSMALLINT   0       2       ""   1     EmployeeID               ""
2       SQLNCHAR      2       100     ""   2     Name                     SQL_Latin1_General_CP1_CI_AS
3       SQLNCHAR      2       100     ""   3     Title                    SQL_Latin1_General_CP1_CI_AS
4       SQLNCHAR      2       100     ""   4     Background               SQL_Latin1_General_CP1_CI_AS

Pour plus d’informations, consultez Utilisez les fichiers de format non XML (SQL Server).

B. Utiliser un fichier de format non-XML

Le fichier de format XML suivant utilise le format de données natif SQL Server de la table HumanResources.myTeam. Ce fichier de format a été créé à l'aide de la commande bcp suivante.

bcp AdventureWorks2022.HumanResources.myTeam format nul -f myTeam.xml -x -n -T

Le fichier de format contient :

<?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="NativePrefix" LENGTH="1"/>
  <FIELD ID="2" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="EmployeeID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Title" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="Background" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>

Pour plus d’informations, consultez Fichiers de format XML (SQL Server).

Quand faut-il utiliser un format de fichier ?

Les fichiers de format sont généralement requis dans les circonstances suivantes :

  • Lorsque vous utilisez une instruction INSERT ... SELECT * FROM OPENROWSET(BULK...).

  • Pour des situations complexes d’importation en bloc à l’aide de bcp ou BULK INSERT.

  • Le même fichier de données est utilisé comme source pour plusieurs tables possédant des schémas différents.

  • Le fichier de données contient un nombre de champs différent du nombre de colonnes dans la table cible. Par exemple :

    • La table cible contient au moins une colonne pour laquelle une valeur par défaut est définie ou une valeur NULL est autorisée.
    • Les utilisateurs n'ont pas les autorisations SELECT/INSERT sur une ou plusieurs colonnes de la table.
    • Un seul fichier de données est utilisé avec au moins deux tables dont les schémas sont différents.

  • L'ordre des colonnes est différent dans le fichier des données et la table.

  • Les caractères de fin ou les longueurs de préfixes sont différents dans les colonnes du fichier de données.

Remarque

S’il n’existe pas de fichier de format et si une commande bcp définit un commutateur de format de données (-n, -c, -w ou -N) ou BULK INSERT si une opération définit l’option DATAFILETYPE, le format de données défini est utilisé comme méthode par défaut pour interpréter les champs du fichier de données.

Contenu connexe