6

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

Two columns are nullable and the Id is identity.

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)

  • Here is an extract of the csv:

    ID, IDStopLine, IDException, Hour, PositionHour, Day

,28, 8, 12:20, 52, 0

,29, 163, , 1,

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

enter image description here

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
Diogo
  • 316
  • 5
  • 11
  • that's not even a bulk insert statement, the example uses a SP with the "insert" "values" nomenclature – Diogo Sep 19 '12 at 17:30

2 Answers2

2

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)
Aleksandr Fedorenko
  • 16,594
  • 6
  • 37
  • 44
1

To insert "NULL" instead of "Default value of Col2", you need to use the -k switch or KEEPNULL option, as demonstrated in the following bcp and BULK INSERT examples.

USE AdventureWorks;
GO
BULK INSERT MyTestDefaultCol2
   FROM 'C:\MyTestEmptyField2-c.Dat'
   WITH (
      DATAFILETYPE = 'char',
      FIELDTERMINATOR = ',',
      KEEPNULLS
   );
GO

http://msdn.microsoft.com/en-us/library/ms187887.aspx

Xordal
  • 1,369
  • 13
  • 29