Mancata corrispondenza del tipo T-SQL BULK INSERT

Question

Sto cercando di eseguire un semplice BULK INSERT da un file CSV di grandi dimensioni a una tabella. La tabella e il file hanno colonne corrispondenti. Questo è il mio codice:

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

Come puoi vedere ho provato con i terminatori di riga \ne 0x0a(e molti altri)

Continuo a ricevere un errore di mancata corrispondenza del tipo:

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 colonna createdAtè di tipo datetime:

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

Questi sono i valori della createdAtcolonna presi dalle prime tre righe:

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

Ho anche provato con un formato numerico diverso come suggerito. Ho anche provato a cambiare il tipo di colonna in DATETIME2(n):

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

Non ho idea di cos'altro rivedere.

Gradirei qualsiasi aiuto.

Grazie!

Answer 1

Esistono molti formati di valori letterali stringa da convertire in date e ore supportati da SQL Server: vedere la documentazione in linea di MSDN su CAST e CONVERT . La maggior parte di questi formati dipende dalle impostazioni che hai, quindi, queste impostazioni potrebbero funzionare alcune volte e talvolta no. E il DATETIMEtipo di dati in particolare è notoriamente esigente riguardo a quali formati di stringhe letterali funzionano - e quali altri (la maggior parte) non lo fanno ... DATETIME2(n)è molto più indulgente e meno esigente da affrontare!

Il modo per risolvere questo problema è utilizzare il formato della data ISO-8601 (leggermente adattato) supportato da SQL Server: questo formato funziona sempre , indipendentemente dalla lingua di SQL Server e dalle impostazioni del formato della data.

Il formato ISO-8601 è supportato da SQL Server è disponibile in due versioni:

• YYYYMMDDsolo per le date (nessuna porzione di tempo); nota qui: niente trattini! , è molto importante! NONYYYY-MM-DD è indipendente dalle impostazioni dateformat nel tuo SQL Server e NON funzionerà in tutte le situazioni!

o:

• YYYY-MM-DDTHH:MM:SSper date e ore - nota qui: questo formato ha trattini (ma possono essere omessi) e un Tdelimitatore fisso tra la parte data e ora del tuo file DATETIME.

Ciò è valido per SQL Server 2000 e versioni successive.

Se utilizzi SQL Server 2008 o più recente e il DATEtipo di dati (solo DATE- no DATETIME !), Puoi effettivamente utilizzare anche il YYYY-MM-DDformato e funzionerà anche con qualsiasi impostazione nel tuo SQL Server.

Non chiedermi perché l'intero argomento è così complicato e un po 'confuso - è così. Ma con il YYYYMMDDformato, dovresti andare bene per qualsiasi versione di SQL Server e per qualsiasi lingua e impostazione del formato di data nel tuo SQL Server.

Si consiglia di utilizzare SQL Server 2008 e versioni successive DATEse è necessaria solo la parte relativa alla data e DATETIME2(n)quando sono necessarie sia la data che l'ora. Dovresti provare a eliminare gradualmente il DATETIMEtipo di dati, se possibile

Nel tuo caso, proverei una delle due cose:

• se puoi - usa DATETIME2(n)invece che DATETIMEcome tipo di dati della tua colonna - questo da solo potrebbe risolvere tutti i tuoi problemi

• se non puoi utilizzare DATETIME2(n), prova a utilizzare 2020-08-22T13:51:57invece di 2020-08-22 13:51:57specificare la data e l'ora nel file di importazione CSV.