SQL BULK INSERT Tutorial 🎯

Welcome to our SQL Bulk Insert tutorial! This guide is designed for beginners and intermediate learners, so let's dive right in. By the end of this lesson, you'll be able to import large datasets into your SQL tables efficiently using the BULK INSERT statement.

What is SQL BULK INSERT? 📝

The BULK INSERT statement is a SQL Server command used for loading large amounts of data into a table from a data file. It is more efficient than using the traditional INSERT INTO statement when dealing with huge datasets.

Why use SQL BULK INSERT? 💡

Faster data loading: BULK INSERT can process large datasets more quickly than traditional INSERT INTO statements.
Reduced memory usage: BULK INSERT reads data directly from the file and does not require temporary storage in memory.
Error handling: The BULK INSERT statement provides error messages and rows affected information, making it easier to troubleshoot data loading issues.

Requirements 📝

SQL Server Management Studio (SSMS) or any other SQL client tool
A SQL Server database and a table to store the data
A text file containing the data to be imported (CSV, TXT, etc.)

SQL BULK INSERT Syntax 📝

sql

BULK INSERT table_name
FROM 'path_to_file'
WITH (FORMATFILE = 'format_file_path', DATAFILE = 'data_file_path', FIELDTERMINATOR = ',', ROWTERMINATOR = '\n');

Format File 📝

The format file is an XML file that defines the structure of the data file. You can create a format file manually or use the built-in SQL Server tool to generate one.

Example 💡

Let's walk through an example of using BULK INSERT to import data from a CSV file.

Create a table to store the data:

sql

CREATE TABLE employees (
    Id INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Age INT,
    Salary DECIMAL(18, 2)
);

Create a CSV file with employee data:

1,John,Doe,30,50000.00
2,Jane,Smith,28,60000.00
3,Mike,Johnson,35,70000.00

Save the CSV file as employees.csv in the project directory.
Create a format file for the CSV:

xml

<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <FORMATFILE="format_file_name.xml"
    CODEPAGE="1252"
    ROW_DELIMITOR="0x0A"
    FIELD_TERMINATOR="0x2C"
  />
  <RECORD>
    <FIELD ID="1" xsi:type="CharTerm" LENGTH="1"/>
    <FIELD ID="2" xsi:type="CharTerm" LENGTH="50"/>
    <FIELD ID="3" xsi:type="CharTerm" LENGTH="50"/>
    <FIELD ID="4" xsi:type="Int4" />
    <FIELD ID="5" xsi:type="Currency" />
  </RECORD>
</BCPFORMAT>

Save the format file as employees_format.xml in the project directory.
Execute the BULK INSERT command in SSMS:

sql

BULK INSERT employees
FROM 'employees.csv'
WITH (FORMATFILE = 'employees_format.xml');

That's it! You've successfully imported data into your SQL table using the BULK INSERT statement.