1

I want to bulk insert from a csv file to a table. This is my code for creating the table

create table customers 
(
    customerNumber int not null,
    customerName varchar(50) not null,
    salesRepNumber int null, 
    primary key(customerNumber)
);

This is the example of the csv file:

1001,John,1121
1002,James,1122
1003,Jonas,1123
1004,Jane,1124,
1005,Tom,1125,
1006,Bob,1126
1007,Thomas,NULL

This is the code for inserting data into the table:

BULK INSERT customers
FROM 'D:\Customers.csv'
WITH (fieldterminator=',', rowterminator='\n')

And I get these errors:

Msg 4864, Level 16, State 1, Line 4
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 12 (salesRepNumber).

Msg 4864, Level 16, State 1, Line 4
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 22, column 12 (salesRepNumber).

I am not so sure about how to bulk insert 'NULL' into a table and I am only supposed to use int datatype for the salesRep.

mmm000
  • 89
  • 8
  • 1
    https://stackoverflow.com/questions/24942538/sql-wont-insert-null-values-with-bulk-insert – Honeyboy Wilson Jul 27 '20 at 18:18
  • Hi, I changed the "NULL" to empty, but am still not able to insert all the data. The code is `BULK INSERT customers FROM 'D:\DENG\Customers.csv' WITH (fieldterminator=',', rowterminator='\n',KEEPNULLS)` I still get this error message: Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 37, column 12 (salesRepNumber). Msg 4864, Level 16, State 1, Line 4 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 110, column 12 (salesRepNumber). – mmm000 Jul 28 '20 at 02:19
  • What is your SQL Server version? – Zhorov Jul 28 '20 at 06:44
  • It is Microsoft SQL Server Management Studio 18, the version is 18.6 – mmm000 Jul 28 '20 at 07:08

0 Answers0