Article
09/27/2010

BULK INSERT (Transact-SQL)

Importe un fichier de données dans une table ou vue de base de données dans un format spécifié par l'utilisateur.

Icône Lien de rubrique Conventions de syntaxe Transact-SQL

Syntaxe

BULK INSERT 
   [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ] 
      FROM 'data_file' 
     [ WITH 
        ( 
   [ [ , ] BATCHSIZE = batch_size ] 
      [ [ , ] CHECK_CONSTRAINTS ] 
   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] 
      [ [ , ] DATAFILETYPE = 
      { 'char' | 'native'| 'widechar' | 'widenative' } ] 
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ] 
   [ [ , ] FIRSTROW = first_row ] 
   [ [ , ] FIRE_TRIGGERS ] 
   [ [ , ] FORMATFILE = 'format_file_path' ] 
   [ [ , ] KEEPIDENTITY ] 
   [ [ , ] KEEPNULLS ] 
   [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ] 
   [ [ , ] LASTROW = last_row ] 
   [ [ , ] MAXERRORS = max_errors ] 
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ] 
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ] 
   [ [ , ] ROWTERMINATOR = 'row_terminator' ] 
   [ [ , ] TABLOCK ] 
   [ [ , ] ERRORFILE = 'file_name' ] 
        )]

Arguments

database_name
Nom de la base de données qui contient la table ou la vue spécifiée. S'il n'est pas spécifié, la base de données actuelle est utilisée.
schema_name
Nom du schéma de la table ou de la vue. L'argument schema_name est facultatif si le schéma par défaut de l'utilisateur réalisant l'opération d'importation en bloc est le schéma de la table ou de la vue spécifiée. Si schema n'est pas spécifié et si le schéma par défaut de l'utilisateur réalisant l'opération d'importation en bloc diffère de celui de la table ou de la vue spécifiée, SQL Server retourne un message d'erreur et l'opération d'importation en bloc est annulée.
table_name
Nom de la table ou de la vue dans laquelle les données doivent être importées en bloc. Seules des vues dans lesquelles toutes les colonnes font référence à la même table de base peuvent être utilisées. Pour plus d'informations sur les restrictions relatives au chargement de données dans les vues, consultez INSERT (Transact-SQL).
'data_file'
Chemin d'accès complet du fichier de données contenant les données à importer dans la table ou la vue spécifiée. BULK INSERT peut importer des données à partir d'un disque (réseau, disquette, disque dur, etc.).

data_file doit spécifier un chemin d'accès valide à partir du serveur où est installé SQL Server. Si data_file correspond à un fichier distant, spécifiez le nom UNC (Universal Naming Convention). Un nom UNC se présente sous la forme \\nom_système\nom_partage\chemin\nom_fichier. Par exemple, \\SystemX\DiskZ\Sales\update.txt.
BATCHSIZE **=**batch_size
Indique le nombre de lignes contenues dans un lot. Chaque lot est copié sur le serveur comme une transaction unique. En cas d'échec, SQL Server valide ou annule la transaction pour chaque lot. Par défaut, toutes les données du fichier spécifié constituent un seul lot. Pour plus d'informations sur les performances, consultez la section « Remarques » plus loin dans cette rubrique.

Pour plus d'informations, consultez Gestion de traitements pour l'importation en bloc.
CHECK_CONSTRAINTS
Spécifie que toutes les contraintes sur la table ou vue cible doivent être vérifiées pendant l'opération d'importation en bloc. Sans l'option CHECK_CONSTRAINTS, toute contrainte CHECK et FOREIGN KEY est ignorée. Après l'opération, la contrainte sur la table est marquée comme non approuvée.

[!REMARQUE]

Les contraintes UNIQUE, PRIMARY KEY et NOT NULL sont toujours appliquées.

À un certain moment, il devient nécessaire d'examiner les contraintes définies sur l'ensemble de la table. Si la table n'était pas vide avant l'opération d'importation en bloc, le coût de la revalidation de la contrainte peut dépasser celui de l'application des contraintes CHECK aux données incrémentielles.

Vous aurez peut-être intérêt à désactiver les contraintes (comportement par défaut) si des données d'entrée contiennent des lignes qui violent les contraintes. Une fois les contraintes CHECK désactivées, vous pouvez importer les données, puis utiliser les instructions Transact-SQL pour supprimer les données non valides.

[!REMARQUE]

L'option MAXERRORS ne s'applique pas à la vérification des contraintes.

[!REMARQUE]

Dans SQL Server 2005 et les versions ultérieures, BULK INSERT impose une validation et des contrôles de données nouveaux qui pourraient faire échouer les scripts existants si ces derniers s'exécutent sur des données non valides dans un fichier de données.

Pour plus d'informations, consultez Contôle de la vérification des contraintes par les opérations d'importation en bloc.

CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }
Indique la page de codes des données dans le fichier. L'utilisation de CODEPAGE n'est justifiée que si les données contiennent des colonnes de type char, varchar ou text dont les valeurs de caractères sont supérieures à 127 ou inférieures à 32.

[!REMARQUE]

Microsoft vous recommande de spécifier un nom de classement pour chaque colonne dans un fichier de format.

Valeur CODEPAGE	Description
ACP	Les colonnes de type char, varchar ou text sont converties de la page de codes ANSI/Microsoft Windows (ISO 1252) à la page de codes SQL Server.
OEM (valeur par défaut)	Les colonnes de type char, varchar ou text sont converties de la page de codes du système OEM à la page de codes SQL Server.
RAW	Aucune conversion d'une page de codes vers une autre ne se produit ; ceci est l'option la plus rapide.
code_page	Numéro de la page de codes, par exemple 850. Important SQL Server ne prend pas en charge la page de codes 65001 (codage UTF-8).

Pour plus d'informations, consultez Copie de données entre différents classements.

DATAFILETYPE = { 'char' | 'native' | 'widechar' | 'widenative' }
Spécifie que BULK INSERT réalise l'opération d'importation en utilisant la valeur définie pour le type de fichier de données.

Valeur DATAFILETYPE	Toutes les données représentées dans :
char (valeur par défaut)	Format caractères. Pour plus d'informations, consultez Utilisation du format caractère pour importer ou exporter des données.
native	Données (base de données) de type natif. Créez le fichier de données natif en important en bloc les données à partir de SQL Server à l'aide de l'utilitaire bcp. La valeur native offre de meilleures performances que la valeur char. Pour plus d'informations, consultez Utilisation du format natif pour importer ou exporter des données.
widechar	Caractères Unicode. Pour plus d'informations, consultez Utilisation du format caractère Unicode pour importer ou exporter des données.
widenative	Données (base de données) de type natif, à l'exception des colonnes de type char, varchar et text, dans lesquelles les données sont stockées au format Unicode. Créez le fichier de données widenative en important en bloc les données à partir de SQL Server à l'aide de l'utilitaire bcp. La valeur widenative offre de meilleures performances que la valeur widechar. Si le fichier de données contient des caractères étendus ANSI, sélectionnez widenative. Pour plus d'informations, consultez Utilisation du format natif Unicode pour importer ou exporter des données.

FIELDTERMINATOR ='field_terminator'
Spécifie la marque de fin de champ à utiliser pour les fichiers de données de type char et widechar. Par défaut, c'est le caractère de tabulation (\t). Pour plus d'informations, consultez Spécification des indicateurs de fin de champ et de fin de ligne.
FIRSTROW **=**first_row
Numéro de la première ligne à charger. La valeur par défaut est la première ligne du fichier de données spécifié. FIRSTROW commence à 1.

[!REMARQUE]

L'attribut FIRSTROW n'est pas destiné à ignorer les en-têtes de colonnes. Le fait d'ignorer des en-têtes n'est pas pris en charge par l'instruction BULK INSERT. Si des lignes sont ignorées, le moteur de base de données SQL Server examine uniquement les marques de fin de champ et ne valide pas les données figurant dans les champs des lignes ignorées.
FIRE_TRIGGERS
Spécifie que tous les déclencheurs d'insertion définis sur la table de destination seront exécutés au cours de l'opération d'importation en bloc. Si des déclencheurs sont définis pour les opérations INSERT réalisées sur la table cible, ils sont activés à la fin de chaque lot.

Si FIRE_TRIGGERS n'est pas spécifié, aucun déclencheur d'insertion ne s'exécute.

Pour plus d'informations, consultez Contrôle de l'exécution des déclencheurs lors de l'importation de données en bloc.
FORMATFILE ='format_file_path'
Spécifie le chemin d'accès complet au fichier de format. Un fichier de format décrit le fichier de données qui contient les réponses stockées créées à l'aide de l'utilitaire bcp dans la même table ou vue. Le fichier de format doit être utilisé dans les contextes suivants :
- Le fichier de données contient plus ou moins de colonnes que la table ou la vue.
- Les colonnes sont ordonnées différemment.
- Les délimiteurs de colonne sont différents.
- Le format des données présente d'autres changements. Les fichiers de format sont généralement créés au moyen de l'utilitaire bcp, puis modifiés, au besoin, à l'aide d'un éditeur de texte. Pour plus d'informations, consultez Utilitaire bcp.
KEEPIDENTITY
Indique que la ou les valeurs d'identité figurant dans le fichier de données importé doivent être utilisées dans la colonne d'identité. Si KEEPIDENTITY n'est pas spécifié, les valeurs d'identité de cette colonne sont vérifiées mais pas importées, et SQL Server affecte automatiquement des valeurs uniques en fonction de la valeur initiale et d'un incrément spécifié lors de la création de la table. Si le fichier de données ne contient pas de valeurs pour la colonne d'identité de la table ou de la vue, utilisez un fichier de format pour indiquer que la colonne d'identité ne doit pas être prise en compte lors de l'importation des données ; dans ce cas, SQL Server attribue automatiquement des valeurs uniques à la colonne. Pour plus d'informations, consultez DBCC CHECKIDENT (Transact-SQL).

Pour plus d'informations sur la conservation des valeurs d'identité, consultez Conservation des valeurs d'identité lors de l'importation de données en bloc.
KEEPNULLS
Spécifie que pendant l'importation en bloc les colonnes vides doivent conserver la valeur NULL au lieu d'insérer des valeurs par défaut dans ces colonnes. Pour plus d'informations, consultez Conservation des valeurs NULL ou utilisation des valeurs par défaut lors de l'importation en bloc.
KILOBYTES_PER_BATCH = kilobytes_per_batch
Indique le nombre approximatif de kilo-octets (Ko) de données par lot sous la forme kilobytes_per_batch. Par défaut, KILOBYTES_PER_BATCH est inconnu. Pour plus d'informations sur les performances, consultez la section « Remarques » plus loin dans cette rubrique.

Pour plus d'informations, consultez Gestion de traitements pour l'importation en bloc.
LASTROW**=**last_row
Numéro de la dernière ligne à charger. La valeur par défaut est 0, c'est-à-dire la dernière ligne du fichier de données spécifié.
MAXERRORS = max_errors
Nombre maximal d'erreurs de syntaxe tolérées dans les données avant l'annulation de l'importation en bloc. Chaque ligne ne pouvant pas être importée par l'opération d'importation en bloc est ignorée et compte comme une erreur. Si max_errors n'est pas spécifié, la valeur par défaut est de 10.

[!REMARQUE]

L'option MAX_ERRORS ne s'applique pas aux vérifications de contraintes ni à la conversion des données de type money et bigint.
ORDER ( { column [ ASC | DESC ] } [ ,... n ] )
Indique l'ordre de tri des données dans le fichier. Les performances de l'importation en bloc seront meilleures si les données en cours d'importation sont triées d'après l'index cluster de la table, s'il en existe un. Si le fichier de données est trié dans un autre ordre (c'est-à-dire pas dans l'ordre d'une clé d'index cluster) ou s'il n'y a pas d'index cluster dans la table, la clause ORDER est ignorée. Les noms de colonnes fournis doivent être des noms de colonnes valides dans la table de destination. Par défaut, l'opération d'insertion en bloc considère que le fichier de données n'est pas ordonné. Pour une importation en bloc optimisée, SQL Server valide également le fait que les données importées sont triées.

Pour plus d'informations, consultez Contrôle de l'ordre de tri lors de l'importation de données en bloc.
n
Espace réservé qui indique que plusieurs colonnes peuvent être spécifiées.
ROWS_PER_BATCH **=**rows_per_batch
Nombre approximatif de lignes de données que compte le fichier de données.

Par défaut, toutes les données du fichier de données sont envoyées au serveur en une seule transaction, et le nombre de lignes du lot est inconnu de l'optimiseur de requête. Si vous spécifiez ROWS_PER_BATCH (valeur > 0), le serveur utilise cette valeur pour optimiser l'importation en bloc. La valeur spécifiée pour ROWS_PER_BATCH devrait correspondre à peu près au nombre réel de lignes. Pour plus d'informations sur les performances, consultez la section « Remarques » plus loin dans cette rubrique.

Pour plus d'informations, consultez Gestion de traitements pour l'importation en bloc.
ROWTERMINATOR ='row_terminator'
Spécifie le délimiteur de fin de ligne à utiliser pour les fichiers de données de type char et widechar. Par défaut, il s'agit de \r\n (caractère de nouvelle ligne). Pour plus d'informations, consultez Spécification des indicateurs de fin de champ et de fin de ligne.
TABLOCK
Spécifie qu'un verrou de niveau table est acquis pour la durée de l'opération d'importation en bloc. Une table peut être chargée simultanément par plusieurs clients à condition qu'elle ne comporte pas d'index et que TABLOCK soit spécifié. Par défaut, le comportement du verrouillage est déterminé par l'option table lock on bulk load. En verrouillant la table pendant la durée de l'importation en bloc, vous réduisez les conflits de verrouillage et augmentez considérablement, dans certains cas, les performances. Pour plus d'informations sur les performances, consultez la section « Remarques » plus loin dans cette rubrique.

Pour plus d'informations, consultez Contrôle du mécanisme de verrouillage pour l'importation en bloc.
ERRORFILE ='file_name'
Fichier utilisé pour collecter les lignes comportant des erreurs de mise en forme et impossibles à convertir en un ensemble de lignes OLE DB. Ces lignes sont copiées « telles quelles » du fichier de données vers ce fichier d'erreur.

Le fichier d'erreur est créé lors de l'exécution de la commande. Une erreur se produit si le fichier existe déjà. De plus, un fichier de contrôle portant l'extension .ERROR.txt est créé. Il fait référence à chacune des lignes du fichier d'erreur et propose un diagnostic. Dès que les erreurs ont été corrigées, les données peuvent être chargées.

Notes

Pour obtenir une comparaison de l'instruction BULK INSERT, de l'instruction INSERT ... SELECT * FROM OPENROWSET(BULK...) et de la commande bcp, consultez À propos des opérations d'exportation et d'importation en bloc.

Pour plus d'informations sur la préparation de données pour une importation en bloc, telles que la configuration requise pour importer des données à partir d'un fichier de données CSV, consultez Préparation des données en vue d'une exportation ou d'une importation en bloc.

L'instruction BULK INSERT peut être exécutée à l'intérieur d'une transaction définie par l'utilisateur. La restauration d'une transaction définie par l'utilisateur qui fait appel à une instruction BULK INSERT et à une clause BATCHSIZE pour importer des données dans une table ou une vue à l'aide de plusieurs lots entraîne la restauration de l'ensemble des lots envoyés à SQL Server.

Pour obtenir des informations sur les opérations d'insertion de lignes qui sont exécutées par importation en bloc et enregistrées dans le journal des transactions, consultez Conditions requises pour une journalisation minimale dans l'importation en bloc.

Dans SQL Server 2005 et les versions ultérieures, BULK INSERT impose une validation et un contrôle nouveaux et plus stricts des données lues dans un fichier qui pourraient faire échouer les scripts existants si ces derniers s'exécutent sur des données non valides. Par exemple, BULK INSERT vérifie désormais que :

la représentation en mode natif des données de type float ou real est valide ;
les données Unicode présentent une taille en octets paire.

L'importation en bloc de certaines données incorrectes, qui était autorisée dans les versions antérieures de SQL Server, risque de ne plus l'être dorénavant. Dans les versions antérieures de SQL Server, l'échec n'avait pas lieu tant qu'un client n'essayait pas d'accéder aux données non valides. Une validation plus stricte réduit les risques d'incidents lors de l'interrogation des données après l'importation en bloc.

Considérations relatives aux performances

Si le nombre de pages à vider dans un lot unique dépasse un seuil interne, une analyse complète du pool de mémoires tampons peut s'effectuer afin d'identifier les pages à vider lors de la validation du lot. Cette analyse complète peut nuire aux performances de l'importation en bloc. Le dépassement du seuil interne se produit lorsqu'un pool de mémoires tampons de grande taille est associé à un sous-système d'E/S lent. Pour éviter tout dépassement de mémoire tampon sur les ordinateurs de grande capacité, évitez d'utiliser l'indication TABLOCK (qui entraîne la suppression des optimisations en bloc) ou utilisez une taille de lot plus petite (qui permet de conserver les optimisations en bloc).

En raison de la diversité des ordinateurs, nous vous recommandons de tester différentes tailles de lot avec votre charge de données pour trouver la solution la plus adaptée.

Importation et exportation en bloc de documents SQLXML

Pour exporter ou importer en bloc des données SQLXML, utilisez l'un des types de données ci-dessous dans votre fichier de format :

Type de données	Effet
SQLCHAR ou SQLVARYCHAR	Les données sont envoyées dans la page de codes du client ou dans la page de codes inhérente au classement. Le résultat est le même que si vous définissiez la propriété DATAFILETYPE ='char' sans spécifier de fichier de format.
SQLNCHAR ou SQLNVARCHAR	Les données sont envoyées au format Unicode. Le résultat est le même que si vous définissiez la propriété DATAFILETYPE = 'widechar' sans spécifier de fichier de format.
SQLBINARY ou SQLVARYBIN	Les données sont envoyées sans être converties.

Conversion du type String en type Decimal

Dans SQL Server 2005 et les versions ultérieures, les conversions de type String (chaîne) en Decimal (décimal) utilisées dans BULK INSERT suivent les mêmes règles que la fonction Transact-SQLCONVERT, qui rejette les chaînes représentant des valeurs numériques qui utilisent la notation scientifique. BULK INSERT traite donc ce genre de chaînes comme des valeurs non valides et signale des erreurs de conversion.

[!REMARQUE]

Dans SQL Server version 7.0 et SQL Server 2000, BULK INSERT prend en charge la conversion de type chaîne en type décimal pour les chaînes représentant des valeurs numériques à notation scientifique.

Pour contourner ce problème, utilisez un fichier de format permettant d'importer en bloc des données de type float à notation scientifique dans une colonne décimale. Dans le fichier de format, décrivez explicitement la colonne comme étant de type real ou float. Pour plus d'informations sur ces types de données, consultez float et real (Transact-SQL).

[!REMARQUE]

Les fichiers de format représentent les données real sous le type de données SQLFLT4 et les données float sous le type de données SQLFLT8. Pour plus d'informations sur les fichiers de format XML, consultez Syntaxe de schéma pour les fichiers de format XML ; pour plus d'informations sur les fichiers de format non XML, consultez Spécification du type de stockage de fichier à l'aide de bcp.

Exemple d'importation d'une valeur numérique qui utilise la notation scientifique

Cet exemple utilise la table suivante :

CREATE TABLE t_float(c1 float, c2 decimal (5,4))

L'utilisateur veut importer des données en bloc dans la table t_float. Le fichier de données, C:\t_float-c.dat, contient des données float à notation scientifique ; par exemple :

8.0000000000000002E-28.0000000000000002E-2

Cependant, l'instruction BULK INSERT ne peut pas importer ces données directement dans t_float, car sa deuxième colonne, c2, utilise le type de données decimal. Un fichier de format est donc nécessaire, il doit faire correspondre les données float à notation scientifique au format décimal de la colonne c2.

Le fichier de format suivant utilise le type de données SQLFLT8 pour établir la correspondance entre le deuxième champ de données et la deuxième colonne :

<?xml version="1.0"?>

Si vous voulez opter pour ce fichier de format (avec le nom de fichier C:\t_floatformat-c-xml.xml) afin d'importer les données de test dans la table de test, exécutez l'instruction Transact-SQL suivante :

BULK INSERT bulktest..t_float
FROM 'C:\t_float-c.dat' WITH (FORMATFILE='C:\t_floatformat-c-xml.xml');
GO

Autorisations

Requiert les autorisations INSERT et ADMINISTER BULK OPERATIONS. De plus, l'autorisation ALTER TABLE est nécessaire si une ou plusieurs des conditions suivantes sont réunies :

Des contraintes existent et l'option CHECK_CONSTRAINTS n'est pas spécifiée.

[!REMARQUE]

La désactivation des contraintes est le comportement par défaut. Pour vérifier les contraintes explicitement, utilisez l'option CHECK_CONSTRAINTS.
Des déclencheurs existent et l'option FIRE_TRIGGER n'est pas spécifiée.

[!REMARQUE]

Par défaut, les déclencheurs ne sont pas activés. Pour activer les déclencheurs explicitement, utilisez l'option FIRE_TRIGGER.
Vous utilisez l'option KEEPIDENTITY pour importer une valeur d'identité du fichier de données.

Délégation de compte de sécurité (emprunt d'identité)

Si un utilisateur SQL Server s'est connecté à l'aide de l'authentification Windows, il n'est autorisé à lire que les fichiers accessibles par le biais de son compte d'utilisateur, quel que soit le profil de sécurité du processus SQL Server.

Si vous exécutez l'instruction BULK INSERT à l'aide de sqlcmd ou osql à partir d'un ordinateur, en insérant les données dans SQL Server sur un deuxième ordinateur et en spécifiant un argument data_file sur un troisième ordinateur sous forme de chemin UNC, vous recevrez probablement une erreur 4861.

Pour résoudre cette erreur, utilisez l'authentification SQL Server et spécifiez une connexion SQL Server utilisant le profil de sécurité du compte de processus SQL Server ou configurez Windows de façon à activer la délégation de compte de sécurité. Pour plus d'informations sur l'activation d'un compte d'utilisateur en vue de son approbation pour la délégation, consultez l'aide de Windows.

Pour plus d'informations sur ce point et sur d'autres questions de sécurité relatives à l'utilisation de BULK INSERT, consultez Importation de données en bloc à l'aide de BULK INSERT ou OPENROWSET(BULK...).

Exemples

A. Importation des données d'un fichier à l'aide du caractère (|)

L'exemple suivant importe des informations détaillées de commande dans la table AdventureWorks.Sales.SalesOrderDetail, à partir du fichier de données spécifié en utilisant le caractère | comme marque de fin de champ et |\n comme marque de fin de ligne.

BULK INSERT AdventureWorks.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH 
      (
         FIELDTERMINATOR =' |',
         ROWTERMINATOR =' |\n'
      )

B. Utilisation de l'argument FIRE_TRIGGERS

L'exemple suivant spécifie l'argument FIRE_TRIGGERS.

BULK INSERT AdventureWorks.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH
     (
        FIELDTERMINATOR =' |',
        ROWTERMINATOR = ':\n',
        FIRE_TRIGGERS
      )

C. Utilisation du retour à la ligne comme délimiteur de ligne

L'exemple suivant importe un fichier qui utilise le retour à la ligne comme marque de fin de ligne, par exemple une sortie UNIX :

DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = 'BULK INSERT AdventureWorks.Sales.SalesOrderDetail
FROM ''<drive>:\<path>\<filename>'' 
WITH (ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@bulk_cmd)

Autres exemples

Les rubriques suivantes fournissent d'autres exemples de la commande BULK INSERT :

Historique des modifications

Mise à jour du contenu
Ajout d'informations sur les performances dans la section « Remarques ».
Mise à jour de la description de l'option FIRSTROW.
Mise à jour de la description de code_page.

Voir aussi

Partager via