Partager via

Utiliser le format natif pour importer ou exporter des données (SQL Server)

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

Il est recommandé d’utiliser le format natif quand vous transférez des données en bloc entre plusieurs instances de SQL Server au moyen d’un fichier de données qui ne contient pas de caractères d’un jeu de caractères étendus/codés sur deux octets (DBCS).

Remarque

Pour transférer en masse des données entre plusieurs instances de SQL Server en utilisant un fichier de données contenant des caractères étendus ou DBCS, vous devez utiliser le format natif Unicode. Pour plus d’informations, consultez Utiliser le format natif Unicode pour importer ou exporter des données (SQL Server).

Le format natif préserve les types de données native d'une base de données. Le format natif est destiné aux transferts de données à haute vitesse entre des tables SQL Server. Si vous utilisez un fichier de format, les tables source et cible ne doivent pas nécessairement être identiques. Le transfert de données se déroule en deux étapes :

  1. exportation en bloc des données d'une table source vers un fichier de données ;

  2. importation en bloc des données d'un fichier de données vers la table cible.

L'utilisation d'un format natif entre tables identiques permet de gagner du temps et de l'espace puisque les conversions superflues des types de données à partir de et vers le format caractère sont éliminées. Pour atteindre la vitesse de transfert optimale, quelques contrôles sont effectués au niveau du formatage des données. Pour empêcher que des problèmes ne surviennent au niveau des données chargées, tenez compte des restrictions suivantes.

Limites

Pour importer des données au format natif, veillez à ce que les conditions suivantes soient réunies :

  • Le fichier de données doit être au format natif.

  • Soit la table cible doit être compatible avec le fichier de données (qui doit présenter le nombre voulu de colonnes, avec les mêmes types de données, longueur de colonne, état NULL, etc.), soit vous devez utiliser un fichier de format pour mapper chacun des champs sur sa colonne correspondante.

    Remarque

    Si vous importez des données d'un fichier qui ne concorde pas avec la table cible, l'importation peut réussir mais les valeurs de données insérées dans la table cible peuvent être incorrectes. En effet, les données du fichier sont interprétées en se basant sur le format de la table cible. Par conséquent, tout défaut de correspondance entraîne l'insertion de valeurs incorrectes. Cependant, en aucun cas une telle non-correspondance peut-elle provoquer des incohérences logiques ou physiques dans la base de données.

    Pour plus d’informations sur l’utilisation de fichiers de format, consultez Format des fichiers pour importer ou exporter des données (SQL Server).

Une importation réussie n'endommage pas la table cible.

Traitement des données au format natif par l’utilitaire bcp

Cette section comporte des remarques spécifiques liées à la manière dont l'utilitaire bcp exporte et importe les données au format natif.

  • Données non-caractère

    L’utilitaire bcp emploie le format de données binaires interne de SQL Server pour écrire des données qui ne sont pas de type caractère d’une table vers un fichier de données.

  • Donnéeschar ou varchar

    Au début de chaque champ char ou varchar , bcp ajoute la longueur du préfixe.

    Important

    En mode natif, l’utilitaire bcp convertit par défaut les caractères de SQL Server en caractères OEM avant de les copier dans un fichier de données. L’utilitaire bcp convertit les caractères d’un fichier de données en caractères ANSI avant de les importer en bloc dans une table SQL Server. Au cours de ces conversions, il est possible que des données caractères étendus soient perdues. Dans le cas de caractères étendus, vous devez soit utiliser le format natif Unicode, soit spécifier une page de codes.

  • Donnéessql_variant

    Si des données sql_variant sont stockées en tant que SQLVARIANT dans un fichier de données au format natif, elles conservent l’ensemble de leurs caractéristiques. Les métadonnées qui enregistrent le type de données de chaque valeur de données sont stockées en même temps que celle-ci. Les métadonnées sont employées pour recréer la valeur de données avec le même type de données dans une colonne sql_variant de destination.

    Si le type de données de la colonne de destination n’est pas sql_variant, chaque valeur de données est convertie dans le type de données de la colonne de destination, suivant les règles normales de la conversion implicite de données. Si une erreur survient pendant la conversion des données, le traitement actif est annulé. Toutes les valeurs char et varchar transférées entre les colonnes sql_variant peuvent rencontrer des problèmes de conversion de page de codes.

    Pour plus d’informations sur la conversion de données, consultez conversion de type de données (moteur de base de données).

Options de commande pour le format natif

Vous pouvez importer des données au format natif dans une table à l’aide de l’utilitaire bcp ou des instructions BULK INSERT ou INSERT... SELECT * FROM OPENROWSET(BULK...). Si vous utilisez une commande bcp ou une instruction BULK INSERT, vous pouvez spécifier le format de données dans l’instruction. Pour une instruction INSERT... SELECT * FROM OPENROWSET(BULK...) , vous devez spécifier le format de données dans un fichier de format.

Le format natif est pris en charge par les options de commande suivantes :

Commande Choix Descriptif
bcp -n Force l’utilitaire bcp à utiliser les types de données natifs des données.*
BULK INSERT DATAFILETYPE ='native' Utilise les types de données native ou widenative des données. DATAFILETYPE n’est pas nécessaire si un fichier de format spécifie les types de données.
OPENROWSET N/A Doit utiliser un fichier de format.

* Pour charger des données natives dans-n un format compatible avec les versions antérieures des clients SQL Server, utilisez le -V commutateur. Pour plus d’informations, consultez Importer des données de format natif et de caractères à partir de versions antérieures de SQL Server.

Remarque

Vous pouvez également spécifier le formatage par champ dans un fichier de format. Pour plus d’informations, consultez Format de fichiers pour importer ou exporter des données (SQL Server).

Exemples de conditions de test

Les exemples utilisent les fichiers de base de données et de format créés dans cet article.

Exemple de table

Le script ci-dessous crée une base de données test, une table nommée myNative et remplit la table avec des valeurs initiales. Exécutez l’instruction Transact-SQL suivant dans Microsoft SQL Server Management Studio (SSMS) :

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.myNative ( 
   PersonID smallint NOT NULL,
   FirstName varchar(25) NOT NULL,
   LastName varchar(30) NOT NULL,
   BirthDate date,
   AnnualSalary money
   );

-- Populate table
INSERT TestDatabase.dbo.myNative
VALUES 
(1, 'Anthony', 'Grosse', '1980-02-23', 65000.00),
(2, 'Alica', 'Fatnowna', '1963-11-14', 45000.00),
(3, 'Stella', 'Rossenhain', '1992-03-02', 120000.00);

-- Review Data
SELECT * FROM TestDatabase.dbo.myNative;

Exemple de fichier de format non XML

SQL Server prend en charge deux types de fichier de format : format XML et format non XML. Le format non XML est le format d’origine pris en charge dans les versions précédentes de SQL Server. Pour plus d’informations, consultez Utiliser des fichiers de format non XML (SQL Server). La commande suivante utilise l’utilitaire bcp pour générer un fichier de format non xml myNative.fmtbasé sur le schéma de myNative. Pour utiliser une commande bcp pour créer un fichier de format, spécifiez l’argument format et utilisez nul au lieu d’un chemin d’accès au fichier de données. L’option de format nécessite également l’option -f. En outre, pour cet exemple, le qualificateur c est utilisé pour spécifier des données de caractères 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 les commandes suivantes :

bcp TestDatabase.dbo.myNative format nul -f D:\BCP\myNative.fmt -T 

REM Review file
Notepad D:\BCP\myNative.fmt

Important

Vérifiez que votre fichier de format non XML se termine par un retour charriot\saut de ligne. Sinon, vous recevez probablement le message d’erreur suivant :

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format file

Exemples

Les exemples utilisent les fichiers de base de données et de format créés dans cet article.

Utiliser bcp et le format natif pour exporter des données

Commutateur -n et commande OUT.

Le fichier de données créé dans cet exemple sera utilisé dans les exemples suivants.

À partir d’une invite de commandes, entrez les commandes suivantes :

bcp TestDatabase.dbo.myNative OUT D:\BCP\myNative.bcp -T -n

REM Review results
NOTEPAD D:\BCP\myNative.bcp

Utiliser bcp et un format natif pour importer des données sans fichier de format

Commutateur -n et commande IN.

À partir d’une invite de commandes, entrez les commandes suivantes :

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNative;"

REM Import data
bcp TestDatabase.dbo.myNative IN D:\BCP\myNative.bcp -T -n

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNative;"

Utiliser bcp et un format natif pour importer des données avec un fichier de format non XML

Les commutateurs -n et -f et la commande IN.

À partir d’une invite de commandes, entrez les commandes suivantes :

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNative;"

REM Import data
bcp TestDatabase.dbo.myNative IN D:\BCP\myNative.bcp -f D:\BCP\myNative.fmt -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNative;"

Utiliser BULK INSERT et un format natif sans fichier de format

Argument DATAFILETYPE.

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

TRUNCATE TABLE TestDatabase.dbo.myNative; -- for testing
BULK INSERT TestDatabase.dbo.myNative
    FROM 'D:\BCP\myNative.bcp'
    WITH (
        DATAFILETYPE = 'native'
        );

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

Utiliser BULK INSERT et un format natif avec un fichier de format non XML

Argument FORMATFILE.

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

TRUNCATE TABLE TestDatabase.dbo.myNative; -- for testing
BULK INSERT TestDatabase.dbo.myNative
   FROM 'D:\BCP\myNative.bcp'
   WITH (
        FORMATFILE = 'D:\BCP\myNative.fmt'
        );

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

Utiliser OPENROWSET et un format natif avec un fichier de format non XML

Argument FORMATFILE.

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

TRUNCATE TABLE TestDatabase.dbo.myNative;  -- for testing
INSERT INTO TestDatabase.dbo.myNative
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myNative.bcp', 
        FORMATFILE = 'D:\BCP\myNative.fmt'  
        ) AS t1;

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

Pour utiliser des formats de données pour l’importation en bloc ou l’exportation en bloc :

Contenu connexe