Несоответствие типа T-SQL BULK INSERT

Question

Я пытаюсь сделать простую BULK INSERT из большого файла CSV в таблицу. Столбцы таблицы и файла совпадают. Это мой код:

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

Как видите, я пробовал использовать терминаторы строк \nи 0x0a(и многое другое)

Я все время получаю ошибку несоответствия типа:

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

Колонка createdAtбывает типа datetime:

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

Это значения createdAtстолбца, взятые из первых трех строк:

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

Я также попробовал использовать другой числовой формат, как было предложено. Я также попытался изменить тип столбца на DATETIME2(n):

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

Я понятия не имею, что еще рассмотреть.

Буду признателен за любую помощь.

Благодаря!

Answer 1

SQL Server поддерживает множество форматов строковых литералов, которые можно преобразовать в дату и время - см. Электронную документацию MSDN по CAST и CONVERT . Большинство из этих форматов зависят от того, какие настройки у вас есть, поэтому эти настройки могут работать несколько раз, а иногда и нет. И DATETIMEтип данных, в частности, общеизвестно придирчиво к тому, какие форматы строковых литералов работают - а какие другие (большинство) нет ... DATETIME2(n)гораздо более снисходительны и менее требовательны к работе!

Чтобы решить эту проблему, используйте (слегка адаптированный) формат даты ISO-8601, который поддерживается SQL Server - этот формат работает всегда - независимо от языка SQL Server и настроек формата даты.

Формат ISO-8601, поддерживаемый SQL Server, бывает двух видов:

• YYYYMMDDтолько для свиданий (без временной части); примечание: без тире! , это очень важно! YYYY-MM-DDэто НЕ зависит от настроек DateFormat в вашем SQL Server и НЕ работать в любых ситуациях!

или же:

• YYYY-MM-DDTHH:MM:SSдля даты и времени - обратите внимание: в этом формате есть дефисы (но их можно не указывать) и фиксированный Tразделитель между датой и временем вашего DATETIME.

Это действительно для SQL Server 2000 и новее.

Если вы используете SQL Server 2008 или новее и DATEтип данных (только DATE- нет DATETIME !), То вы действительно можете использовать этот YYYY-MM-DDформат, и он будет работать с любыми настройками вашего SQL Server.

Не спрашивайте меня, почему вся эта тема такая запутанная и запутанная - так оно и есть. Но с YYYYMMDDформатом вас должно быть хорошо для любой версии SQL Server и для любого языка и настройки формата даты в вашем SQL Server.

Рекомендация для SQL Server 2008 и новее - использовать, DATEесли вам нужна только часть даты, и DATETIME2(n)когда вам нужны как дата, так и время. Вы должны попытаться постепенно отказаться от DATETIMEтипа данных, если это возможно.

В вашем случае я бы попробовал одно из двух:

• если можете - используйте DATETIME2(n)вместо типа DATETIMEданных столбца - это может решить все ваши проблемы.

• если не можете использовать DATETIME2(n)- попробуйте использовать 2020-08-22T13:51:57вместо 2020-08-22 13:51:57указания даты и времени в файле импорта CSV.