T-SQL批量插入类型不匹配 [英] T-SQL BULK INSERT type mismatch

问题描述

我正在尝试从大型CSV文件到表格进行简单的批量插入.该表和文件具有匹配的列.这是我的代码:

I am trying to do a simple BULK INSERT from a large CSV file to a table. The table and the file have matching columns. This is my code:

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

如您所见，我已经尝试使用行终止符 \ n 和 0x0a (还有更多)

As you can see I have tried with row terminators \n and 0x0a (and a bunch more)

我不断收到类型不匹配错误:

I keep getting a type mismatch error:

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 列的值，该值取自前三行:

These are the values of the createdAt column as taken from the first three rows:

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

我还尝试使用建议的其他数字格式.我还尝试将列类型更改为 DATETIME2(n):

I also tried with a different number format as suggested. I also tried changing the column type to DATETIME2(n):

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

我不知道还有什么要回顾的.

I have no idea what else to review.

我将不胜感激.

谢谢！

推荐答案

有许多格式的字符串文本可以转换为日期&SQL Server支持的时间-请参见有关CAST和CONVERT的MSDN联机丛书.这些格式大多数都依赖，取决于您所拥有的设置-因此，这些设置可能有时会起作用-有时不起作用.尤其是 DATETIME 数据类型对字符串文字的哪些格式有效，以及其他(大多数)不起作用的格式挑剔.. DATETIME2(n)非常多更多的宽容和更少的挑剔！

There are many formats of string literals to be converted to dates & times supported by SQL Server - see the MSDN Books Online on CAST and CONVERT. Most of those formats are dependent on what settings you have - therefore, these settings might work some times - and sometimes not. And the DATETIME datatype in particular is notoriously picky about what formats of string literals work - and which others (most) don't.... DATETIME2(n) is much more forgiving and less picky to deal with!

解决此问题的方法是使用SQL Server支持的(略有适应性的) ISO-8601日期格式-该格式始终有效 -无论您使用哪种格式SQL Server语言和日期格式设置.

The way to solve this is to use the (slightly adapted) ISO-8601 date format that is supported by SQL Server - this format works always - regardless of your SQL Server language and dateformat settings.

SQL Server支持 ISO-8601格式有两种口味:

The ISO-8601 format is supported by SQL Server comes in two flavors:

• YYYYMMDD 仅用于日期(无时间部分)；请在此处注意:不要破折号！，这很重要！ YYYY-MM-DD 是否，独立于SQL Server中的dateformat设置，并且不能在所有情况下均有效！

• YYYYMMDD for just dates (no time portion); note here: no dashes!, that's very important! YYYY-MM-DD is NOT independent of the dateformat settings in your SQL Server and will NOT work in all situations!

或:

• YYYY-MM-DDTHH:MM:SS 用于日期和时间-请在此处注意:此格式具有破折号(但它们可以省略)，并在 DATETIME 的日期和时间部分之间使用固定的 T 作为分隔符.

• YYYY-MM-DDTHH:MM:SS for dates and times - note here: this format has dashes (but they can be omitted), and a fixed T as delimiter between the date and time portion of your DATETIME.

这对SQL Server 2000及更高版本有效.

This is valid for SQL Server 2000 and newer.

如果您使用SQL Server 2008或更高版本，并且使用 DATE 数据类型(仅 DATE -不 DATETIME ！)，那么实际上您也可以使用 YYYY-MM-DD 格式，并且该格式也可以在您的SQL Server中进行任何设置.

If you use SQL Server 2008 or newer and the DATE datatype (only DATE - not DATETIME!), then you can indeed also use the YYYY-MM-DD format and that will work, too, with any settings in your SQL Server.

不要问我，为什么整个主题如此棘手且有些令人困惑-这就是事实.但是使用 YYYYMMDD 格式，则可以使用任何版本的SQL Server以及SQL Server中的任何语言和日期格式设置.

Don't ask me why this whole topic is so tricky and somewhat confusing - that's just the way it is. But with the YYYYMMDD format, you should be fine for any version of SQL Server and for any language and dateformat setting in your SQL Server.

对于SQL Server 2008和更高版本，建议仅在需要日期部分的情况下使用 DATE ，在需要日期和时间的情况下使用 DATETIME2(n).如果可能的话，您应该尝试逐步淘汰 DATETIME 数据类型

The recommendation for SQL Server 2008 and newer is to use DATE if you only need the date portion, and DATETIME2(n) when you need both date and time. You should try to start phasing out the DATETIME datatype if ever possible

就您而言，我将尝试以下两种方法之一:

In your case, I'd try one of two things:

• 如果可以的话-使用 DATETIME2(n)代替 DATETIME 作为列的数据类型-仅此一项就可以解决所有问题

• if you can - use DATETIME2(n) instead of DATETIME as your column's datatype - that alone might solve all your problems

如果您不能使用 DATETIME2(n)-尝试使用 2020-08-22T13:51:57 而不是 2020-08-22 13:51:57 用于在CSV导入文件中指定日期和时间.

if you can't use DATETIME2(n) - try to use 2020-08-22T13:51:57 instead of 2020-08-22 13:51:57 for specifying your date&time in the CSV import file.

这篇关于T-SQL批量插入类型不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！