D’une façon générale, l’insertion BULK permet d’insérer en masse des gros volumes de données.
L’instruction BULK INSERT entre dans cette catégorie mais a une vocation toute particulière puisqu’elle insert des données depuis un fichier CSV (en mode BULK).
Sa syntaxe est simple :
BULK INSERT dbo.Destination FROM 'C:\input.txt' WITH (<options>)
Il existe la même fonctionnalité sous la forme d’une application en ligne de commande fournie avec SQL Server : bcp.exe
Passons au sujet principal de l’article : la gestion de la longueur des chaines de caractères.
Par défaut, SQL Server va tenter de mapper les champs du fichier CSV aux champs de la table en convertissant implicitement. Evidemment, l’instruction lèvera des erreurs en conséquence car même si ça marche bien, ne faites jamais confiance aux données à intégrer (l’une de mes devises).
Il est donc recommandé de fournir toutes les informations pour la bonne intégration des données et cela se fait en fournissant un fichier de format décrivant les champs (type, longueur, séparateur, etc.).
Si vous êtes un utilisateur averti de SQL Server, vous savez que la troncature de chaine est considérée comme une erreur. BULK INSERT n’y échappe pas et vous remontera des erreurs si votre CSV contient des données plus longues que ce qui a été déclaré.
Donc comment forcer une troncature automatique des données ?
Ne tournons pas autour du pot. La solution est de ne pas préciser de MAX_LENGTH dans le fichier de format et de désactiver les ANSI_WARNINGS lors de l’exécution.
SET ANSI_WARNINGS OFF
C’est surtout la désactivation des warnings qui permet la troncature mais je vous recommande de retirer l’option MAX_LENGTH car si les données sont plus longues, BULK INSERT considèrera que vous souhaitez que cela soit une anomalie et lèvera une erreur.
Mais j’ai encore une erreur…
Par défaut, MAX_LENGTH a une valeur de 8000 (pour un varchar, 4000 pour du nvarchar) et donc vous aurez tout de même une erreur si votre colonne est plus volumineuse.
Une solution est de préciser le type SQLTEXT (type varchar(max)) dans la description de la colonne destination.
<ROW><COLUMN xsi:type="SQLTEXT"/></ROW>
Et pas besoin de mettre votre colonne en type varchar(max) dans la table destination, la troncature se fera automatiquement à la bonne taille.
La déclaration dans le fichier format permet juste de zapper l’erreur.
On récapitule
Pour tronquer automatiquement les chaines de caractères importées d’un CSV avec l’instruction BULK INSERT, il faut :
- désactiver les longueurs maximum (MAX_LENGTH) dans le fichier format)
- désactiver les warnings ANSI (SET ANSI_WARNINGS OFF)
- indiquer SQLTEXT comme type destination dans le fichier format pour les données supérieures à 8ko
