How To Use BULK INSERT In SQL Server? T-SQL

The BULK INSERT command in T-SQL (Transact-SQL) is a powerful feature for importing data from flat files into SQL Server tables. It’s essential for database administrators and developers who are often required to populate tables in an optimized manner. This article will guide you through the different aspects of using BULK INSERT with practical examples.

Table of Contents

Introduction to the SQL Server Bulk Insert statement

BULK INSERT is a T-SQL command that allows for the bulk import of data from flat files into SQL Server tables. It’s especially useful for quickly inserting large sets of data. This feature is a highly efficient T-SQL command designed specifically to import large volumes of data into SQL Server tables from flat files, such as CSV or TXT formats. Unlike traditional methods of data import that handle one record at a time, BULK INSERT processes data in batches, thereby significantly speeding up the overall import process.

Bulk Insert generic syntax

To get you started, let’s delve into the basic syntax and a simple example. This first example is a generic one and simply explains the basic syntax and arguments.

BULK INSERT target_table  
FROM 'data_file'  
WITH (  
<bulk_insert_option> [, ...]  
)

Bulk Insert practical example in T-SQL

Here’s a T-SQL code example demonstrating how to quickly bulk insert data into a table named dbo.Products_Staging.

-- BULK INSERT to load a flat txt or CSV file into a database
BULK INSERT dbo.Products_Staging
FROM 'C:\FlatFile.txt'
WITH (
   FIELDTERMINATOR = ',', 
   ROWTERMINATOR = '\n', 
   FIRSTROW = 2
);

Use XML format files for better flexibility

If your source data file follows a specific format, you may need to define that format for the BULK INSERT operation to proceed smoothly. The two most common ways to do this are by using a Format File or by specifying the terminators for rows and fields directly in the command. You can define a format file to specify the layout of your source data file. The format file can be XML-based or non-XML-based. Use the FORMATFILE argument with the location to an XML format file.

-- Using a Format File 
BULK INSERT dbo.Products_Staging
FROM 'C:\FlatFile.txt'  
WITH (  
  FORMATFILE='C:\FormatFile.xml'
);

Bulk Insert format file example

Certainly, creating a format file is crucial for customizing how BULK INSERT will interact with your data file. The format file instructs SQL Server how to interpret the flat file’s structure. Below is an example of what a FormatFile.xml might look like when configuring it for a CSV file that matches a hypothetical Products table with three columns: ProductID, ProductName, and Price.

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="10"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="20"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="ProductID" xsi:type="SQLINT"/>
  <COLUMN SOURCE="2" NAME="ProductName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Price" xsi:type="SQLFLOAT"/>
 </ROW>
</BCPFORMAT>

Sure, let’s break it down in simpler terms:

The RECORD tag is like a rule book for each line in your text file. It tells SQL Server what to expect when it looks at a single line in your file.
The FIELD one is a smaller part of that gives more details. It says what marks the end of each piece of information in a line (like a comma between two words) and how long each piece can be.
The ROW tag however connects the pieces of information in your text file to specific places in your SQL table.
Then the COLUMN XML tag represents a smaller part of that gives more specifics. It tells SQL Server what kind of data it’s looking at (like numbers or words) and where to put it in the table.

So, RECORD and FIELD are about understanding the text file, while ROW and COLUMN are about loading that information into the target SQL Server table. By using a format file like this, you provide a blueprint to SQL Server for understanding how to read your flat file and populate the target table. This method is particularly useful when your flat file’s structure doesn’t perfectly align with your SQL table’s schema or when you’re working with complex data types.

Bulk Insert common options

Here are four of the most commonly used options you can specify with BULK INSERT.

FIELDTERMINATOR: Specifies the field delimiter.
ROWTERMINATOR: Specifies the row delimiter.
FIRSTROW: Specifies the starting row for data import, i.e. skip or any existing header.
ERRORFILE: Specifies the file to log any errors.

For instance, the ERRORFILE option can be used as follows:

-- Logging errors to a log file
BULK INSERT dbo.Products_Staging
FROM 'C:\FlatFile.txt'
WITH (
   FIELDTERMINATOR = ',', 
   ROWTERMINATOR = '\n',
   ERRORFILE = 'C:\error.log'
);

SQL Server Bulk Insert best practices

To make the most out of the BULK INSERT feature with smooth T-SQL execution, follow these three recommandations:

Ensure your source file and target table schema match.
Use batch sizes judiciously for performance tuning.
Always backup your target table before performing bulk insert operations.

Conclusion on the Bulk Insert command

BULK INSERT in T-SQL is a handy tool for database administrators and developers who need to populate tables swiftly and efficiently. By understanding the syntax and options available, you can leverage its full potential for various data import tasks. You can always go deeper and read the official documentation about the feature. The Bulk insert is also available in SSIS, also with specific options. Loading large files in a challenge in all SQL database types.

Of course, feel free to leave your questions and thoughts in the comments section below, we’d be glad to answer.