SQL Server BULK INSERT - 插入日期时间值

Question

我有600万行数据要插入到SQL Server数据库中。我可以用6百万个INSERT语句方法慢慢地插入数据（根据我的计算需要18小时才能运行），或者我可以尝试使用BULK INSERT。

BULK INSERT存在无法转义字符的问题，但是在这种情况下，数据非常简单，所以不应该遇到此问题。

然而，SQL Server似乎不喜欢将任何形式的日期/时间数据插入到字段中。

以下是表格（伪SQL）：

CREATE TABLE Tasks (
    TaskId bigint NOT NULL IDENTITY(1,1) PRIMARY KEY,
    TriggerId bigint NOT NULL FOREIGN KEY,
    Created datetime NOT NULL,
    Modified datetime NOT NULL,
    ScheduledFor datetime NULL,
    LastRan datetime NULL,
    -- and about 10 more fields after this
)

这是我的批量插入语句：

SET DATEFORMAT dmy
BULK INSERT Tasks
FROM 'C:\TasksBulk.dat'
WITH (
    -- CHECK_CONSTRAINTS is not necessary as the only constraints are always enforced regardless of this option (UNIQUE, PRIMARY KEY, and NOT NULL)
    CODEPAGE = 'RAW',
    DATAFILETYPE = 'native',

    KEEPIDENTITY,
    MAXERRORS = 1,
    ORDER ( CallId ASC ),

    FIELDTERMINATOR = '\t',
    ROWTERMINATOR   = '\0'
)

这是TasksBulk.dat中的第一行数据：

1000\t1092\t01/01/2010 04:00:17\t01/01/2010 04:00:17\t\t01/01/2010 04:00:14\0

(为了易读性，重新格式化，并用4个空格替换制表符：)

(为了易读性，重新格式化，并用4个空格替换制表符：)

1000    1092    01/01/2010 04:00:17    01/01/2010 04:00:17        01/01/2010 04:00:14\0

然而，当我运行BULK INSERT语句时，出现以下错误：

Msg 4864, Level 16, State 1, Line 2 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 3 (Created).

我尝试了不同的行终止符和字段终止符以及各种不同的日期/时间格式（包括“01/01/2010”，“2010-01-01”，都带有或不带有“04:00:17”时间组件）。我不知道我在这里做错了什么。

- Dai

Answer 1

更改DATAFILETYPE从'native'到'char'解决了问题。'native'类型意味着对所有内容都有严格的数据格式要求，而'char'则适用于更多的纯文本文件。

- Dai

---

Stack Overflow说我只能在48小时后接受自己的答案。顺便说一下，谢谢你之前的帮助。 - Dai

Answer 2

你的CODDEPAGE设置为RAW（可能是为了提高速度）。

错误信息表明你的数据包含了超出该代码页范围的字符。

CODEPAGE [ = 'ACP' | 'OEM' | 'RAW' | 'code_page' ]

Specifies the code page of the data in the data file. CODEPAGE is relevant only if the data contains char, varchar, or text columns with character values greater than 127 or less than 32.

但这可能会误导。您的示例数据行包含一个缺失的列。如果您不使用格式文件，则必须使用表中的每个字段。

因此，您可以创建格式文件或创建一个暂存表，其中datetime列为varchar（25），导入数据，然后从暂存表执行更新到目标表。这样，您就可以更好地控制转换和缺失数据。

- Mitch Wheat

---

无论是CODEPAGE选项还是DATAFILETYPE选项，我都会收到相同的错误。在两个日期之间的“缺失列”表示ScheduledFor列中的NULL值。 - Dai

我知道它代表着一个NULL值！我的意思是，BULK INSERT希望所有的值都在那里，除非你使用一个格式文件。 - Mitch Wheat

我暂时无法尝试formatfile选项，但我确实尝试了您创建分段表的建议（仅使用简单的varchar/int数据类型）。我没有遇到转换错误，但现在无论我把行终止符放在哪里，都会不断出现“批量加载：在数据文件中遇到意外的文件结尾。”。唉。（在这方面，MySQL要简单得多，微软怎么回事？） - Dai

@David: "unexpected end of file" 可能是因为BULKINSERT期望每行都有所有的列。 - Mitch Wheat

Answer 3

我熟悉的一种方法是以整数形式插入日期。我使用从某个日期开始的秒数 (我使用了10年前的一个日期作为参考点，因为我将要访问或生成的数据都不会更早)。

例如2012-01-02 12:15:10.000 这个日期将被存储为378637886，使用参考日期为2000年1月1日。

查询数据库时，可以使用DateAdd(SS, column_name, '2000-01-01')返回该列的日期。

如果需要这种精度，则也可以使用毫秒。

我使用自己的自定义函数将我的时间转换为所需格式，并使用另一个自定义函数将日期转换回秒数。

我意识到这可能不是一个好方法，因为它可能需要您进行数据库和代码更改，但也许它是其他人会发现有用的解决方案概念。

- meltdownmonk