Mass insert sql server in null column

Question

I am having trouble entering a null value from a bulk insert statement.

Two columns are NULL, and Id is identity.

An int value with null values ​​is fine, but time does not work.

• Here is the main operator:

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

• Here is an excerpt from csv:

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

The point is that I'm trying to insert zeros in both of these columns. As a result, it is an int column with NULL and a time column from 00:00:00

Answer 1

I don’t know why to insert a temporary column from 00:00:00, but if you need NULL, then you can try to create a trigger and run BULK INSERT with argument FIRE_TRIGGER

 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) 

Answer 2

To insert "NULL" instead of "Default value for Col2", you need to use the -k or KEEPNULL option, as shown 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