Microsoft SQL Server - Export et import de données avec l’utilitaire bcp

Introduction

L’utilitaire bcp dans le client Microsoft SQL Server permet d’exporter et importer des données en mode natif ou en mode caractères. Ce guide pratique s’adresse à tous les administrateurs et concepteurs qui abordent pour la première fois cet outil natif d’export/import de données, outil natif qui est de loin le plus efficace.

Dans le jargon classique :

"bcp out" désigne l’export des données dans un fichier avec bcp.
"bcp in" correspond à l’import des données dans une base depuis un fichier avec bcp.

Classiquement les outils clients MS SQL Server sont installés dans le répertoire C:\Program Files\Microsoft SQL Server\<version>\tools\binn. <version> dépend de la version du client Microsoft SQL Server installé (2000, 2005, 2008…).

SQL Server 2000 : 80

C:\Program Files\Microsoft SQL Server\80\tools\binn)

SQL Server 2005 : 90

C:\Program Files\Microsoft SQL Server\90\tools\binn)

SQL Server 2008 : 100

C:\Program Files\Microsoft SQL Server\100\tools\binn)

SQL Server 2008 R2 : 100

C:\Program Files\Microsoft SQL Server\100\tools\binn)

L’utilitaire bcp est disponible dans le répertoire C:\Program Files\Microsoft SQL Server\<version>\tools\binn, répertoire inscrit dans la variable %PATH% de l’environnement Windows.

Attention : bcp est un binaire qui existe également dans le client Sybase Adaptive Server Enterprise et en fonction de la localisation des répertoires Sybase et Microsoft SQL Server dans la variable d’environnement %PATH%, le binaire bcp de Sybase peut prendre la main au lieu du binaire bcp de Microsoft SQL Server.

Pour adresser ce point, appeler le binaire bcp de Microsoft SQL Server dans une invite de commandes DOS en positionnant C:\Program Files\Microsoft SQL Server\<version>\tools\binn au début de la variable %PATH%.

Voici des exemples pratiques avec le client SQL Server 2005 (version=90).

DOS> C:\Program Files\Microsoft SQL Server\tools\90\binn\bcp -?

ou se positionner dans le répertoire C:\Program Files\Microsoft SQL Server\tools\90\binn :

DOS> cd C:\Program Files\Microsoft SQL Server\tools\90\binn

DOS> bcp -?

Export de données

bcp out

La syntaxe pour exporter une table ou une vue dans un fichier fichier.txt est la suivante :

DOS> bcp basededonnees.proprietaire.matable out fichier.txt -Sserveur
     [-Uutilisateur] [-Pmotdepasse] [-T]
     [-t separateur de champs] [-r separateur de ligne] [-c] [-n]

Le mot clé "out" indique le mode export de données.

Les options -c (mode caractères) et -n (mode natif) sont exclusives l’une de l’autre.

Dans les exemples qui suivent :

base de données : FinanceKiosk
serveur : SRVWINFR1 (instance par défaut)
propriétaire : dbo
table : AUM_PORTFOLIO
utilisateur : sa

bcp out en mode natif (option -n)

L’option -n exporte les données d’une table ou d’une vue en mode natif. Pour exporter la table AUM_PORTFOLIO (propriétaire dbo) en mode natif :

DOS> bcp FinanceKiosk.dbo.AUM_PORTFOLIO out fichier.bcpn -S SRVWINFR1 -Usa -P****** -n
          Starting copy...

37491 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 500  Average = (74982.00 rows per sec.)

Le fichier fichier.bcpn produit est dans un format propriétaire qui n’est pas humainement lisible. Le format en mode natif est adapté pour les transferts de données d’un serveur à un autre serveur ou si la table contient des colonnes de type text (données non structurées) contenant des retours chariot, etc.

bcp out en mode caractères (option -c)

L’option -c exporte les données en mode caractères, le fichier produit est lisible et exploitable (awk, etc.). Avec l’option -c, les options -t (séparateur de champs) et -r (séparateur de lignes) sont utilisables. Pour exporter la table AUM_PORTFOLIO (propriétaire dbo) en mode caractères :

DOS> bcp FinanceKiosk.dbo.AUM_PORTFOLIO out fichier.bcpc -S SRVWINFR1 -Usa -P****** -c

fichier.bcpc

1000005074241368  2009-03-19 00:00:00.000   EUR   187413059.270000000
1000005081554198  2009-03-20 00:00:00.000   EUR   32552630.210000000

Par défaut, le séparateur de champs est la tabulation et le séparateur de lignes est \r\n.

Pour spécifier des séparateurs non standards, par exemple le point virgule comme séparateur de champs et #\r\n comme séparateur de lignes : utiliser les arguments -t et -r.

DOS> bcp FinanceKiosk.dbo.AUM_PORTFOLIO out fichier.bcpc -S SRVWINFR1 -Usa -P****** -t";" -r"#\r\n" -c

fichier.bcpc

1000005074241368;2009-03-19 00:00:00.000;EUR;187413059.270000000#
1000005081554198;2009-03-20 00:00:00.000;EUR;32552630.210000000#

bcp out en mode authentification intégrée SSO (option -T)

Si le compte Windows utilisé (domaine\login) est autorisé à accéder au serveur et à la base de données, l’authentification SQL avec les options -U et -P ne sont plus nécessaires, la commande bcp out peut être utilisée en authentification intégrée avec l’option -T (Trusted Connection) :

DOS> bcp FinanceKiosk.dbo.AUM_PORTFOLIO out fichier.bcpc -S SRVWINFR1 -T -t";" -r"#\r\n" -c

bcp out et les instances nommées

Jusqu’ici l’instance SQL Server est une instance par défaut sur la machine SRVWINFR1. Lorsqu’il s’agit d’une instance nommée, comme par exemple l’instance nommée MOSS_DATA01 sur la machine FRDMOS105, le paramètre -S dans la commande bcp out indiquant le serveur s’écrit -S<machine>\<instance nommée> :

DOS> bcp FinanceKiosk.dbo.AUM_PORTFOLIO out fichier.bcpc 
         -SFRDMOS105\MOSS_DATA01 -Usa -P******  -t";" -r"#\r\n" -c

Exporter les résultats d’une requête ou d’une procédure stockée avec bcp et l’option queryout

Le binaire bcp de SQL Server permet d’exporter des requêtes ou des jeux de résultats de procédures stockées avec l’option queryout. Cette option queryout permet notamment de s’affranchir de la création de vues ou d’exporter très simplement les résultats d’une procédure stockée effectuant des dénormalisations.

DOS> bcp "requete" queryout fichier.txt -Sserveur
     [-Uutilisateur] [-Pmotdepasse] [-T]
     [-t separateur de champs] [-r separateur de ligne] [-c] [-n]

Quelques exemples très simples :

Export de la table AUM_PORTFOLIO avec des clauses WHERE

DOS> bcp "select * from FinanceKiosk.dbo.AUM_PORTFOLIO where PortfolioID > 50000" 
         queryout fichier.bcpc -S SRVWINFR1 -Usa -P******  -t";" -c

Export du résultat d’une procédure stockée

DOS> bcp "exec FinanceKiosk.dbo.uspGetOrderbyPortfolioID @Portolio_id=16"
         queryout fichier.bcpc -S SRVWINFR1 -Usa -P******  -t";" -c

Import de données

bcp in

La syntaxe pour importer dans une table les données provenant d’un fichier fichier.txt est la suivante :

DOS> bcp basededonnees.proprietaire.matable in fichier.txt -Sserveur
     [-Uutilisateur] [-Pmotdepasse] [-T]
     [-t separateur de champs] [-r separateur de ligne] [-b] [-E] [-c] [-n]

Le mot clé "in" indique le mode import de données.

Les options -c (mode caractères) et -n (mode natif) sont exclusives l’une de l’autre.

Comme pour l’export de données avec bcp out :

les séparateurs de colonnes et de lignes sont respectivement indiqués avec les options -t et -r si ceux ci ne sont pas les séparateurs par défaut (tabulation pour le séparateur de colonnes et \r\n pour le séparateur de lignes).
L’option -T (trusted connection) permet de réaliser l’opération en authentification intégrée Windows.
L’option -S s’écrit -S<machine>\<instance nommée> dans le cas d’une instance nommée (ex. : -SFRDMOS105\MOSS_DATA01).

Les options -b et -E sont importantes lors des imports et sont abordées dans les paragraphes qui suivent.

Pour importer dans la table AUM_PORTFOLIO les données provenant d’un fichier texte pour lequel le séparateur de champs est le point virgule et le séparateur de lignes #\r\n :

fichier.bcpc

1;MQ;1#
9;IPS;1#

DOS> bcp FinanceKiosk.dbo.AUM_PORTFOLIO in fichier.bcpc -S SRVWINFR1 -Usa -P****** -t";" -r"#\r\n" -c

bcp in et l’option -b (batch size) : éviter la saturation du journal (log full)

L’import avec bcp dans une table comportant des indexes, des colonnes de type text ou des déclencheurs (triggers) est journalisé. En fonction de la volumétrie à importer, la taille du journal de transactions de la base de données peut être insuffisante.

DOS> bcp FinanceKiosk.dbo.AUM_PORTFOLIO in fichier.bcpc -S SRVWINFR1 -Usa -P****** -t";"  -c
          1000 rows sent to SQL Server. Total sent: 120000
1000 rows sent to SQL Server. Total sent: 121000

La saturation du journal est notifiée par le binaire bcp dans la sortie standard avec l’erreur 9002.

SQLState = 42000, NativeError = 9002
Error = [Microsoft][SQL Native Client][SQL Server]The transaction log for database 'FinanceKiosk' is full.
To find out why space in the log cannot be reused, 
see the log_reuse_wait_desc column in sys.databases

L’option -b (batch size) de la commande bcp in permet d’éviter ces écueils et ordonne de lancer la commande commit toutes les n lignes importées. Pour lancer un commit toutes les 10000 lignes : spécifier -b 10000.

DOS> bcp FinanceKiosk.dbo.AUM_PORTFOLIO in fichier.bcpc -S SRVWINFR1 -Usa -P****** -t";"  -b10000 -c

Le dimensionnement du paramètre "batch size" -b <nb lignes> va dépendre de la taille du journal de la base de données et de la structure de la table (indexes, colonnes text, etc.).

bcp in et l’option -E (colonnes identity)

Lorsque la table contient une colonne de type identity, l’option -E doit être indiquée dans la commande bcp in si l’on souhaite que les identifiants dans le fichier source soient conservés lors de l’import. Sans l’option -E, de nouvelles valeurs pour la colonne identity sont attribuées.

DOS> bcp FinanceKiosk.dbo.AUM_PORTFOLIO in fichier.bcpc -S SRVWINFR1 -Usa -P****** -t";" -b10000 -E -c

L’option -E équivaut à la commande Transact SQL set identity_insert <table> on | off.

L’option -E peut être indiquée dans la commande bcp in même si la table ne contient pas de colonne de type identity.

Depuis SQL Server 2005, si la commande bcp est exécutée avec un utilisateur disposant de droits plus restreints que le rôle dbo (database owner), le droit ALTER doit être donné à cet utilisateur sur la table contenant la colonne identity. Sans ce droit, la commande bcp avec l’option -E est en échec avec le message 1088.

DOS> bcp RPM..SPA in REPORT.csv -t";"  -E -USPA -S SRVWINFR1 -P********** -c
          SQLState = 37000, NativeError = 1088
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot find the
object "RPM..SPA" because it does not exist or you do not have permissions.

Voici la commande pour attribuer le droit ALTER.

grant alter on [proprietaire].[table] to [user]

grant alter on dbo.SPA to RPM_write

Le droit ALTER sur la table est nécessaire si l’une des conditions ci-dessous est remplie :

Des contraintes existent et l’option CHECK_CONSTRAINTS n’est pas donnée ( -h "CHECK_CONSTRAINTS").
Des triggers existent et l’option FIRE_TRIGGER n’est pas donnée ( -h "FIRE_TRIGGER").
L’option -E est donnée pour importer explicitement les valeurs dans une colonne identity.

Gestion des erreurs avec bcp : %ERRORLEVEL%, options -m (maxerrors) et -e (errfile)

Comme tout binaire qui se respecte, bcp retourne un code erreur en cas d’échec, code erreur récupéré par la variable DOS %ERRORLEVEL% à exploiter.

DOS> bcp FinanceKiosk.dbo.AUM_PORTFOLIO in fichier.bcpc -S SRVWINFR1 -Usa -P****** -t";" -b10000
          Starting copy...
 ...
SQLState = 22018, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification

BCP copy in failed

DOS> echo %ERRORLEVEL%
          
1

Par défaut, le nombre maximal d’erreurs autorisé lors de l’import avec bcp in est fixé à 10.

L’option -m (maxerrors) permet d’étendre cette limite à des valeurs personnalisées et plus élevées : par exemple 1000 erreurs maximum rencontrées

DOS> bcp FinanceKiosk.dbo.AUM_PORTFOLIO in fichier.bcpc -S SRVWINFR1 -Usa -P****** -t";" -b10000 -m 1000 -c

Attention : lorsque ce nombre maximal d’erreurs n’est pas atteint, le binaire bcp retourne 0 (succès).

L’option -e indique le fichier d’erreurs dans lequel doivent être consignées les lignes rejetées lors de l’import : pour notifier les erreurs dans le fichier errfile.txt

DOS> bcp FinanceKiosk.dbo.AUM_PORTFOLIO in fichier.bcpc -S SRVWINFR1 -Usa -P****** -t";" -b10000 -e errfile.txt -c

errfile.txt

#@ Row 5, Column 1: Invalid character value for cast specification @#
43659;5;4911-403C-98;1;772;1;2039.9940;.0000

Microsoft SQL Server - Export et import de données avec l’utilitaire bcp, guide pratique