I have already blogged about the usage of BULK INSERT in this post
You can use BULK INSERT command to import data from a file to a table. However if the field seperator is a comma and data has comma as part of it, you may get an error. To avoid this, one method is to make use of OPENROWSET function Consider the following data
"Dodsworth ,t" ,386
Consider the following table
create table test (names varchar(100),no int)
Now try to import data to the table using BULK INSERT command
BULK INSERT test
ROWTERMINATOR = '\n'
The error message is
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 2 (no).
It is becuase the data "Dodsworth ,t" is treated as two columns which results to an error. If you use OPENROWSET function, data will be imported correctly
insert into test(names, no)