Bulk loading data into a database with SQL CLI

16 Oct 2023

References

When working with databases, it’s often necessary to load large volumes of data efficiently. Bulk loading data can significantly speed up the process compared to individual inserts. In this blog post, we’ll explore how to bulk load data into a database using the SQL command-line interface (CLI).

Introduction
Preparing the Data
Using SQL CLI for Bulk Loading
Conclusion

Introduction

Bulk loading is a technique to insert a large amount of data into a database in a single operation, rather than performing one insert statement at a time. This can significantly improve the performance of data insertion, especially when dealing with millions of records.

Preparing the Data

Before proceeding with bulk loading, it’s important to ensure that the data to be loaded is formatted correctly. The data can be in CSV (Comma-Separated Values) format or any other format that can be easily parsed.

For example, let’s consider a CSV file employees.csv with the following structure:

id,first_name,last_name,age,salary
1,John,Doe,30,5000
2,Jane,Smith,28,6000
3,David,Johnson,35,5500

Ensure that the data file is in a format that can be easily parsed by the SQL CLI.

Using SQL CLI for Bulk Loading

Most database systems provide a mechanism to bulk load data. In this example, we’ll focus on the LOAD DATA INFILE command in MySQL.

Connect to the MySQL CLI using the following command:
```
mysql -u username -p
```
Replace username with your MySQL username.

Create a table to hold the data:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  age INT,
  salary DECIMAL(10,2)
);

Load the data from the CSV file into the table using the LOAD DATA INFILE command:
```
LOAD DATA INFILE '/path/to/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
```
Make sure to replace /path/to/employees.csv with the actual path to your data file.
Verify that the data has been successfully loaded:
```
SELECT * FROM employees;
```
You should see the loaded data displayed in the console.

Conclusion

Bulk loading data can significantly improve the performance of inserting large volumes of data into a database. By using the SQL command-line interface and the LOAD DATA INFILE command, you can efficiently load data from CSV files or other formats into your database.

Remember to format your data properly and ensure that you have the necessary permissions to perform bulk loading operations. This technique can save valuable time and resources when dealing with large datasets.

#References:

Table of Contents

Introduction

Preparing the Data

Using SQL CLI for Bulk Loading

Conclusion