Niezgodność typu T-SQL BULK INSERT

Oct 15 2020

Próbuję zrobić prostą WSTAWIANIE BULKOWE z dużego pliku CSV do tabeli. Tabela i plik mają zgodne kolumny. To jest mój kod:

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

Jak widać, próbowałem z terminatorami wierszy \ni 0x0a(i kilkoma innymi)

Ciągle otrzymuję błąd niezgodności typu:

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

Kolumna createdAtjest typu datetime:

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

Oto wartości createdAtkolumny pobrane z pierwszych trzech wierszy:

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

Próbowałem również z innym formatem liczb, zgodnie z sugestią. Próbowałem też zmienić typ kolumny na DATETIME2(n):

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

Nie mam pojęcia, co jeszcze przejrzeć.

Byłbym wdzięczny za każdą pomoc.

Dzięki!

Odpowiedzi

2 marc_s Oct 15 2020 at 16:47

Istnieje wiele formatów literałów ciągów, które można konwertować na daty i godziny obsługiwane przez SQL Server - zobacz MSDN Books Online na temat CAST i CONVERT . Większość tych formatów zależy od tego, jakie masz ustawienia - dlatego te ustawienia mogą czasami działać - a czasami nie. A DATETIMEw szczególności typ danych jest notorycznie wybredny, jeśli chodzi o to, jakie formaty literałów tekstowych działają - a które inne (większość) nie ... DATETIME2(n)jest o wiele bardziej wybaczający i mniej wybredny w obsłudze!

Sposobem na rozwiązanie tego problemu jest użycie (nieznacznie dostosowanego) formatu daty ISO-8601, który jest obsługiwany przez SQL Server - ten format działa zawsze - niezależnie od języka SQL Server i ustawień formatu daty .

Format ISO-8601 jest obsługiwany przez SQL Server w dwóch wersjach:

YYYYMMDDtylko daty (bez części czasu); uwaga: bez myślników! , to bardzo ważne! NIEYYYY-MM-DD jest niezależne od ustawień formatu daty w serwerze SQL i NIE będzie działać we wszystkich sytuacjach!

lub:

YYYY-MM-DDTHH:MM:SSdla dat i godzin - uwaga tutaj: ten format zawiera myślniki (ale można je pominąć) i ustalony Tseparator między datą a godziną w pliku DATETIME.

Dotyczy to SQL Server 2000 i nowszych.

Jeśli używasz SQL Server 2008 lub nowszego i DATEtypu danych (tylko DATE- nie DATETIME !), To rzeczywiście możesz również użyć YYYY-MM-DDformatu, który będzie działał również z dowolnymi ustawieniami w twoim SQL Server.

Nie pytajcie mnie, dlaczego cały ten temat jest taki podstępny i nieco zagmatwany - po prostu tak jest. Ale z YYYYMMDDformatem powinno być w porządku dla każdej wersji SQL Server i dla dowolnego ustawienia języka i formatu daty w SQL Server.

Zaleca się, aby SQL Server 2008 i nowsze wersje używać DATEtylko wtedy, gdy potrzebujesz tylko części daty i DATETIME2(n)kiedy potrzebujesz zarówno daty, jak i godziny. Jeśli to możliwe, powinieneś spróbować rozpocząć wycofywanie tego DATETIMEtypu danych