Nicht übereinstimmende T-SQL BULK INSERT-Typen

Question

Ich versuche, ein einfaches BULK INSERT von einer großen CSV-Datei in eine Tabelle zu erstellen. Die Tabelle und die Datei haben übereinstimmende Spalten. Das ist mein Code:

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

Wie Sie sehen können, habe ich es mit Zeilenabschlusszeichen versucht \nund 0x0a(und ein paar mehr)

Ich erhalte immer wieder einen Typ-Mismatch-Fehler:

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)".

Die Spalte createdAtist vom Typ datetime:

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

Dies sind die Werte der createdAtSpalte aus den ersten drei Zeilen:

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

Ich habe es auch mit einem anderen Zahlenformat versucht, wie vorgeschlagen. Ich habe auch versucht, den Spaltentyp in zu ändern DATETIME2(n):

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

Ich habe keine Ahnung, was ich sonst noch überprüfen soll.

Ich würde mich über jede Hilfe freuen.

Vielen Dank!

Answer 1

Es gibt viele Formate von Zeichenfolgenliteralen, die in von SQL Server unterstützte Datums- und Uhrzeitangaben konvertiert werden können - siehe MSDN Books Online zu CAST und CONVERT . Die meisten dieser Formate hängen davon ab, welche Einstellungen Sie haben - daher funktionieren diese Einstellungen manchmal - und manchmal auch nicht. Und insbesondere der DATETIMEDatentyp ist notorisch wählerisch, welche Formate von String-Literalen funktionieren - und welche anderen (die meisten) nicht ... DATETIME2(n)ist viel verzeihender und weniger wählerisch!

Um dies zu lösen, verwenden Sie das (leicht angepasste) ISO-8601-Datumsformat , das von SQL Server unterstützt wird. Dieses Format funktioniert immer - unabhängig von Ihrer SQL Server-Sprache und den Datumsformateinstellungen.

Das von SQL Server unterstützte ISO-8601-Format gibt es in zwei Varianten:

• YYYYMMDDfür nur Daten (kein Zeitanteil); Beachten Sie hier: keine Striche! , das ist sehr wichtig! YYYY-MM-DDist NICHT unabhängig von den Datumsformateinstellungen in Ihrem SQL Server und funktioniert NICHT in allen Situationen!

oder:

• YYYY-MM-DDTHH:MM:SSTermine und Zeiten - Anmerkung hier: Dieses Format hat Striche (aber sie können weggelassen werden), und ein Fest Tals Trennzeichen zwischen dem Datum und der Zeit Teil Ihrer DATETIME.

Dies gilt für SQL Server 2000 und höher.

Wenn Sie SQL Server 2008 oder neuer und den DATEDatentyp (nur DATE- nicht DATETIME !) Verwenden, können Sie tatsächlich auch das YYYY-MM-DDFormat verwenden, und das funktioniert auch mit allen Einstellungen in Ihrem SQL Server.

Fragen Sie mich nicht, warum dieses ganze Thema so knifflig und etwas verwirrend ist - so ist es eben. Mit dem YYYYMMDDFormat sollten Sie jedoch für jede Version von SQL Server und für jede Sprach- und Datumsformateinstellung in Ihrem SQL Server in Ordnung sein.

Die Empfehlung für SQL Server 2008 und höher lautet, DATEwenn Sie nur den Datumsbereich DATETIME2(n)benötigen und sowohl Datum als auch Uhrzeit benötigen. Sie sollten versuchen, den DATETIMEDatentyp nach Möglichkeit auslaufen zu lassen

In Ihrem Fall würde ich eines von zwei Dingen ausprobieren:

• Wenn Sie - DATETIME2(n)anstelle des DATETIMEDatentyps Ihrer Spalte - verwenden können, kann dies allein alle Ihre Probleme lösen

• Wenn Sie nicht verwenden können, DATETIME2(n)versuchen Sie, 2020-08-22T13:51:57statt 2020-08-22 13:51:57Datum und Uhrzeit in der CSV-Importdatei anzugeben.