Jenis T-SQL BULK INSERT tidak cocok

Question

Saya mencoba melakukan BULK INSERT sederhana dari file CSV besar ke tabel. Tabel dan file memiliki kolom yang cocok. Ini kode saya:

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

Seperti yang Anda lihat, saya telah mencoba dengan terminator baris \ndan 0x0a(dan banyak lagi)

Saya terus mendapatkan kesalahan jenis ketidakcocokan:

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

Kolom createdAtberjenis datetime:

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

Ini adalah nilai createdAtkolom yang diambil dari tiga baris pertama:

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

Saya juga mencoba dengan format angka yang berbeda seperti yang disarankan. Saya juga mencoba mengubah jenis kolom menjadi DATETIME2(n):

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

Saya tidak tahu harus mengulas apa lagi.

Saya sangat menghargai bantuan apa pun.

Terima kasih!

Answer 1

Ada banyak format literal string yang akan dikonversi ke tanggal & waktu yang didukung oleh SQL Server - lihat Buku Online MSDN tentang CAST dan CONVERT . Sebagian besar format tersebut bergantung pada pengaturan yang Anda miliki - oleh karena itu, pengaturan ini mungkin berfungsi beberapa kali - dan terkadang tidak. Dan DATETIMEtipe data khususnya terkenal pilih-pilih tentang format literal string apa yang berfungsi - dan mana yang (sebagian besar) tidak berfungsi .... DATETIME2(n)jauh lebih pemaaf dan kurang pilih-pilih untuk ditangani!

Cara untuk mengatasinya adalah dengan menggunakan format tanggal ISO-8601 (sedikit disesuaikan) yang didukung oleh SQL Server - format ini selalu berfungsi - apa pun pengaturan bahasa dan format tanggal SQL Server Anda.

Format ISO-8601 yang didukung oleh SQL Server hadir dalam dua bentuk:

• YYYYMMDDhanya untuk tanggal (tidak ada porsi waktu); perhatikan di sini: tidak ada tanda hubung! , itu sangat penting! YYYY-MM-DDadalah TIDAK independen dari pengaturan dateformat di SQL Server dan akan tidak bekerja dalam segala situasi!

atau:

• YYYY-MM-DDTHH:MM:SSuntuk tanggal dan waktu - perhatikan di sini: format ini memiliki tanda hubung (tetapi dapat dihilangkan), dan tetap Tsebagai pembatas antara bagian tanggal dan waktu Anda DATETIME.

Ini berlaku untuk SQL Server 2000 dan yang lebih baru.

Jika Anda menggunakan SQL Server 2008 atau yang lebih baru dan DATEtipe data (hanya DATE- tidak DATETIME !), Maka Anda juga dapat menggunakan YYYY-MM-DDformat tersebut dan itu juga akan berfungsi, dengan pengaturan apa pun di SQL Server Anda.

Jangan tanya saya mengapa seluruh topik ini begitu rumit dan agak membingungkan - memang begitulah adanya. Tetapi dengan YYYYMMDDformatnya, Anda akan baik-baik saja untuk versi SQL Server apa pun dan untuk semua bahasa dan pengaturan format tanggal di SQL Server Anda.

Rekomendasi untuk SQL Server 2008 dan yang lebih baru adalah untuk digunakan DATEjika Anda hanya memerlukan bagian tanggal, dan DATETIME2(n)saat Anda memerlukan tanggal dan waktu. Anda harus mencoba untuk mulai menghapus DATETIMEtipe data secara bertahap jika memungkinkan

Dalam kasus Anda, saya akan mencoba salah satu dari dua hal:

• jika Anda bisa - gunakan DATETIME2(n)sebagai pengganti DATETIMEsebagai tipe data kolom Anda - itu saja mungkin menyelesaikan semua masalah Anda

• jika Anda tidak dapat menggunakan DATETIME2(n)- coba gunakan 2020-08-22T13:51:57alih-alih 2020-08-22 13:51:57untuk menentukan tanggal & waktu Anda di file impor CSV.