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 หนังสือออนไลน์ในนักแสดงและแปลง รูปแบบเหล่านี้ส่วนใหญ่ขึ้นอยู่กับการตั้งค่าที่คุณมีดังนั้นการตั้งค่าเหล่านี้อาจใช้งานได้ในบางครั้งและบางครั้งก็ไม่ และ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สำหรับวันที่และเวลา - โน้ตที่นี่: รูปแบบนี้มีขีดคั่น ( แต่พวกเขาสามารถจะละเว้น) และคงเป็นตัวคั่นระหว่างวันที่และเวลาส่วนหนึ่งของคุณTDATETIME

ใช้ได้กับ 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