Passing default values to a column in Bulk insert
I am trying to get data from a csv file with the following data开发者_如何学Go.
Station code;Priority vehicle;DateBegin;DateEnd
01;y;20100214;20100214
02;n;20100214;20100214
03;;20100214;20100214
Now I want a value 'n' in the table when no data is provided for the column 'Priority vehicle' in csv file. I am writing the query as
BULK INSERT dbo.#tmp_station_details
FROM 'C:\station.csv'
WITH (
FIELDTERMINATOR ='';'',
FIRSTROW = 2,
ROWTERMINATOR = ''\n''
)
Check the full explanation here: http://msdn.microsoft.com/en-us/library/ms187887.aspx
"By default, when data is imported into a table, the bcp command and BULK INSERT statement observe any defaults that are defined for the columns in the table. For example, if there is a null field in a data file, the default value for the column is loaded instead. "
My suggestion is to specify a default value for the Priority vehicle column and the Null value from the csv file will be overwritten to your SQL table with the default value specified in the table design.
精彩评论