Working with SQL Server can be rewarding, but it also comes with its fair share of challenges. One common task is importing data from a CSV or text file into a SQL Server table using the BULK INSERT command. However, this process can sometimes lead to errors that can be frustrating to troubleshoot.

In a previous blog post, I discussed how to import a CSV file into SQL Server using BULK INSERT. Since then, I have received numerous comments from readers about various errors they encountered while trying to use this command. In this blog post, I will address some of these errors and provide solutions to fix them.

Error: Msg 4860, Level 16, State 1

This is one of the most common errors reported by readers. It typically occurs when the file being imported does not exist or the file path is incorrect. To reproduce this error, let’s consider the following example:

CREATE TABLE library_books_loan (
    student_id VARCHAR(10) NOT NULL,
    book_id VARCHAR(10) NOT NULL,
    branch_id SMALLINT NOT NULL,
    id_no VARCHAR(10) NOT NULL,
    date_out DATE NULL DEFAULT NULL,
    due_date DATE NULL DEFAULT NULL,
    date_in DATE NULL DEFAULT NULL
)

-- Sample text file to insert into the table
student_id,book_id,branch_id,id_no,date_out,due_date,date_in
1,0399147020,1,9019,2013-11-22,2013-12-06,2013-12-01
2,0030059380,4,9007,2013-12-01,2013-12-15,2013-12-16
3,0671880756,5,9018,2013-12-08,2013-12-22,2013-12-22
4,0911625291,3,9013,2014-01-02,2014-01-16,2014-01-12
5,0688161995,5,9022,2014-02-10,2014-02-24,2014-03-01
6,0911625291,2,9011,2014-03-03,2014-03-17,2014-03-16
7,1861003730,3,9034,2014-04-17,2014-05-01,NULL
12,0911625607,2,9018,2014-04-19,2014-05-03,NULL

If you save this file as “C:\Temp\Books_Library.txt” and run the BULK INSERT command, you may encounter the following error:

Msg 4860, Level 16, State 1, Line 12
Cannot bulk load. The file "C:\Temp\Books_Library.txt.txt" does not exist.

To resolve this error, ensure that the file exists on the machine where SQL Server is running and that the file path is correct on the server itself.

Error: Msg 4864, Level 16, State 1

Another common error that can occur during the BULK INSERT process is a data conversion error. This error typically happens when there is a type mismatch or an invalid character for the specified codepage. Let’s consider the following example:

CREATE TABLE SQLAuth (
    d DATE
)

INSERT INTO SQLAuth VALUES ('NULL')

-- Error: Msg 241, Level 16, State 1, Line 3
-- Conversion failed when converting date and/or time from character string.

In this example, we are trying to insert the string “NULL” into a column defined as a date. This results in a data conversion error. To fix this error, modify the text file and leave the column value empty, which will be treated as NULL automatically.

Here is the modified version of the text file:

student_id,book_id,branch_id,id_no,date_out,due_date,date_in
1,0399147020,1,9019,2013-11-22,2013-12-06,2013-12-01
2,0030059380,4,9007,2013-12-01,2013-12-15,2013-12-16
3,0671880756,5,9018,2013-12-08,2013-12-22,2013-12-22
4,0911625291,3,9013,2014-01-02,2014-01-16,2014-01-12
5,0688161995,5,9022,2014-02-10,2014-02-24,2014-03-01
6,0911625291,2,9011,2014-03-03,2014-03-17,2014-03-16
7,1861003730,3,9034,2014-04-17,2014-05-01,
12,0911625607,2,9018,2014-04-19,2014-05-03,

By running the BULK INSERT command again with the modified file, the data should be inserted successfully, with NULL values in the appropriate columns.

These are just a few examples of common errors that can occur when using the BULK INSERT command in SQL Server. If you encounter any other errors, please feel free to leave a comment, and I will address them in a future blog post.