SQL Server批量插入空值到时间列

本文介绍了SQL Server批量插入空值到时间列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在从大容量插入语句中插入空值时遇到麻烦。

I'm having trouble inserting null value from a bulk insert statement.

两列可以为空，且ID为标识。

Two columns are nullable and the Id is identity.

int 可为null的值可以很好地工作，但 time 不能。

The int nullable workd out fine, but the time doesn't.

• 以下是批量声明：

• Here the bulk statement:

BULK INSERT Circulation FROM '.....file.cs' WITH ( FIRSTROW = 2, MAXERRORS = 0, FIELDTERMINATOR = ',', ROWTERMINATOR = '', KEEPNULLS)

以下是csv的摘录：

Here is an extract of the csv:

ID, IDStopLine, IDException, Hour, PositionHour, Day ,28, 8, 12:20, 52, 0 ,29, 163, , 1,

表示我试图在这两个列中插入空值。结果是int列为NULL和time列为00:00:00

Meaning that I'm trying to insert nulls in those both columns. The result is ithe int column with NULL and the time column with 00:00:00

推荐答案

我不知道为什么在时间列中插入00： 00:00，但如果需要NULL，则可以尝试创建触发器并使用FIRE_TRIGGER参数运行BULK INSERT

I don't know why time column insert with 00:00:00, but if you need NULL then it is possible to try create trigger and run BULK INSERT with FIRE_TRIGGER argument

CREATE TRIGGER dateNull ON dbo.Circulation INSTEAD OF INSERT AS BEGIN INSERT dbo.Circulation(IdStopline, IdException, Hour, PositionHour, Day) SELECT IdStopline, IdException, NULLIF(Hour, '00:00:00.0000000'), PositionHour, Day FROM inserted END BULK INSERT dbo.Circulation FROM 'you_file.csv' WITH (FIRSTROW = 2, MAXERRORS = 0, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', KEEPNULLS, FIRE_TRIGGERS)