Incompatibilité de type T-SQL BULK INSERT

Question

J'essaie de faire un simple BULK INSERT à partir d'un gros fichier CSV vers une table. La table et le fichier ont des colonnes correspondantes. Voici mon code:

BULK INSERT myTable
FROM 'G:\Tests\mySource.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    -- ROWTERMINATOR = '0x0a',
    BATCHSIZE = 1000,
    MAXERRORS = 2
)
GO

Comme vous pouvez le voir, j'ai essayé avec des terminateurs de ligne \net 0x0a(et un tas d'autres)

Je reçois toujours une erreur d'incompatibilité de type:

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 18 (createdAt).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 18 (createdAt).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 18 (createdAt).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (2) was exceeded.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

La colonne createdAtest de type datetime:

CREATE TABLE [dbo].[myTable]
(
    ...
    [createdAt] [datetime] NULL,
    ...
)

Voici les valeurs de la createdAtcolonne extraites des trois premières lignes:

2020-08-22 13:51:57
2020-08-22 14:13:13
2020-08-22 14:16:23

J'ai également essayé avec un format de nombre différent comme suggéré. J'ai également essayé de changer le type de colonne en DATETIME2(n):

2020-08-22T13:51:57
2020-08-22T14:13:13
2020-08-22T14:16:23

Je ne sais pas quoi revoir.

J'apprécierais toute aide.

Merci!

Answer 1

Il existe de nombreux formats de chaînes littérales à convertir en dates et heures prises en charge par SQL Server - consultez la documentation en ligne MSDN sur CAST et CONVERT . La plupart de ces formats dépendent des paramètres dont vous disposez - par conséquent, ces paramètres peuvent parfois fonctionner - et parfois non. Et le DATETIMEtype de données en particulier est notoirement pointilleux sur les formats de littéraux de chaîne qui fonctionnent - et ceux que d'autres (la plupart) ne font pas .... DATETIME2(n)est beaucoup plus indulgent et moins difficile à gérer!

La manière de résoudre ce problème consiste à utiliser le format de date ISO-8601 (légèrement adapté) pris en charge par SQL Server - ce format fonctionne toujours - quels que soient vos paramètres de langue et de format de date SQL Server.

Le format ISO-8601 est pris en charge par SQL Server et se décline en deux versions:

• YYYYMMDDpour juste des dates (aucune portion de temps); remarquez ici: pas de tirets! , c'est très important! YYYY-MM-DDn'est PAS indépendant des paramètres de format de date de votre serveur SQL et ne fonctionnera PAS dans toutes les situations!

ou:

• YYYY-MM-DDTHH:MM:SSpour les dates et les heures - notez ici: ce format a des tirets (mais ils peuvent être omis), et un Tséparateur fixe entre la partie date et heure de votre fichier DATETIME.

Ceci est valable pour SQL Server 2000 et plus récent.

Si vous utilisez SQL Server 2008 ou plus récent et le DATEtype de données (uniquement DATE- pas DATETIME !), Vous pouvez en effet également utiliser le YYYY-MM-DDformat et cela fonctionnera également avec tous les paramètres de votre serveur SQL.

Ne me demandez pas pourquoi tout ce sujet est si délicat et quelque peu déroutant - c'est juste comme ça. Mais avec le YYYYMMDDformat, vous devriez être bien pour n'importe quelle version de SQL Server et pour tout paramètre de langue et de format de date dans votre SQL Server.

La recommandation pour SQL Server 2008 et les versions ultérieures est à utiliser DATEsi vous n'avez besoin que de la partie date et DATETIME2(n)lorsque vous avez besoin à la fois de la date et de l'heure. Vous devriez essayer de commencer à supprimer progressivement le DATETIMEtype de données si possible

Dans votre cas, j'essaierais l'une des deux choses suivantes:

• si vous pouvez - utilisez DATETIME2(n)plutôt DATETIMEque comme type de données de votre colonne - cela seul pourrait résoudre tous vos problèmes

• si vous ne pouvez pas utiliser DATETIME2(n)- essayez d'utiliser 2020-08-22T13:51:57au lieu de 2020-08-22 13:51:57pour spécifier votre date et heure dans le fichier d'importation CSV.