SQL Server批量插入空值到时间列 [英] sql server bulk insert nulls into time column

问题描述

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

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)

这篇关于SQL Server批量插入空值到时间列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！