T-SQL BULK INSERT 유형 불일치

Oct 15 2020

큰 CSV 파일에서 테이블로 간단한 BULK INSERT를 수행하려고합니다. 테이블과 파일에 일치하는 열이 있습니다. 이것은 내 코드입니다.

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

다른 검토 할 내용이 없습니다.

도움을 주시면 감사하겠습니다.

감사!

답변

2 marc_s Oct 15 2020 at 16:47

SQL Server에서 지원하는 날짜 및 시간으로 변환 할 문자열 리터럴 형식은 여러 가지가 있습니다 . CAST 및 CONVERT에 대한 MSDN Books Online을 참조하십시오 . 이러한 형식의 대부분은 사용자의 설정에 따라 달라 지므로 이러한 설정은 때때로 작동 할 수도 있고 작동하지 않을 수도 있습니다. 그리고 DATETIME다른 사람 (대부분의) ....하지 않는다 - 특히 데이터 형식은 문자열 리터럴 작업의 어떤 형식에 대해 까다 롭고 악명입니다 DATETIME2(n)훨씬 더 용서하고 처리 덜 까다 롭고!

이 문제를 해결하는 방법 은 SQL Server에서 지원 하는 (약간 조정 된) ISO-8601 날짜 형식을 사용 하는 것입니다.이 형식은 SQL Server 언어 및 날짜 형식 설정에 관계없이 항상 작동합니다 .

ISO-8601 형식 SQL Server에서 지원되는 두 가지 유형이있다 :

YYYYMMDD날짜 만 (시간 부분 없음) 여기 참고 : 대시 없음! , 그것은 매우 중요합니다! YYYY-MM-DD이다 NOT 당신의 SQL Server에서 DATEFORMAT 설정의 독립적 인 것 NOT 모든 상황에서 작동!

또는:

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가능 하면 데이터 유형을 단계적으로 제거해야 합니다.