Performing a Bulk Insert in SQL Server
Learn how to use the BULK INSERT command to import large data sets into SQL Server.
I recently experienced an issue with the SQL Server Management Studio. I was trying to import a flat file (a CSV file) and could not. I performed a number of steps to ensure that the file was clean and that nothing within my data would have prevented a successful import. Even so, the import process always failed.
The Problem
The resulting error message, which you can see in Figure 1, was misleading. The error pointed to a problem with the data located in a specific row and column. However, the data stored in the specified location was nothing more than a simple text string. There was nothing in that string that should have caused any problems.
error message importing data into SQL Server
Figure 1:This is the error message produced by the import process.
After spending quite a few hours trying to determine the problem, I decided to try something completely different. I had initially intended to use this new technique to gather better diagnostic data, hopefully giving myself a clue as to what was going on. Ultimately, however, this technique solved my problem and successfully imported the data, though I am not entirely sure why it worked and the other technique did not, unless the problem was related to the size of my data set.
Up until this point, I had been trying to use the SQL Server Management Studio's SQL Server Import and Export Wizard to import my data. This wizard walks you through the various steps involved in the data import process and then imports the data on your behalf. I have used this wizard for data import operations in the past, and it has typically been a quick, easy, and painless experience. This time, however, the wizard just wasn't getting the job done.
Related:Guide to Decoding SQL Server Bulk Insert Error Files
Why Use the BULK INSERT Command
That being the case, I decided to try using the BULK INSERT command. The BULK INSERT command is an option for importing large data sets into SQL Server . If you attempt to use the SQL Server Import and Export Wizard to import an excessively large file, the import process can crash or become excessively slow. The BULK INSERT command helps avoid these issues, though these are not the only options for importing data into SQL Server. Other options, which can be appropriate for even larger data sets, include BCP, OPENROWSET, and SSIS .
The reason I chose to use the BULK INSERT command is that it gives you far more control over and insight into errors than the SQL Server Import and Export Wizard does. Here is an example of how you might use the BULK INSERT command:
BULK INSERT <YourTable>
FROM 'C:\Path\File.csv'
WITH (
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
ERRORFILE = 'C:\Path\To\error_log.txt',
MAXERRORS = 1000
)As you can see in Figure 2, you can enter a variation of these commands directly into the SQL Server Management Studio and execute your query from there.
Related:How to Migrate Data to a New SQL Server Database
ntered the BULK INSERT command directly into SQL Server Management Studio
Figure 2:I entered the BULK INSERT command directly into SQL Server Management Studio.
Let's examine this command's function. The first line of code simply specifies the BULK INSERT command, followed by the name of the table into which you want to insert your imported data.
The next line of code uses the FROM command and the path to your CSV file to specify the location and name of the file containing your data.
From there, the code listed above uses the WITH command to tell SQL Server that one or more parameters must be used when executing the BULK INSERT command. These parameters are enclosed in parentheses.
The first of these parameters is FIRSTROW. The FIRSTROW parameter tells SQL Server the number of the first row within your CSV file that contains actual data. Normally, the file's first row contains a header, meaning that the first row containing actual data is row No. 2.
The next parameter is FIELDTERMINATOR. In the sample code above, I am setting FIELDTERMINATOR equal to a comma. Most CSV files use commas to distinguish between data columns, and so the FIELDTERMINATOR=',' command is essentially confirming to SQL that you are importing a comma-separated value file.
The next line contains the ROWTERMINATOR command. The ROWTERMINATOR command tells SQL Server where one record ends and the next one begins. In this case, ROWTERMINATOR is being set to \n, which means that each record starts on a new line.
The second-to-last parameter included in the above code is ERRORFILE. As you may recall, I mentioned that I wanted to use the BULK INSERT command because I thought it might give me greater insight into why the import process was failing. The ERRORFILE parameter causes the BULK INSERT command to produce not one but two text files that can help you track down the source of your problems. I will discuss these text files in another article.
The final parameter used in the code above is MAXERRORS. The MAXERRORS parameter tells the BULK INSERT command how many errors it can ignore before halting the import process. By comparison, the SQL Server Import and Export Wizard will stop the import process if even a single error is encountered.
About the Author
Technology Analyst
Brien Posey is a bestselling technology author, a speaker, and a 20X Microsoft MVP. In addition to his ongoing work in IT, Posey has spent the last several years training as a commercial astronaut candidate in preparation to fly on a mission to study polar mesospheric clouds from space.
You May Also Like