What is Bulk Insert in SQL?

While working with the databases, there is a need to import and export the data from external files. For large datasets, there is an option to load the data in bulk, that is, BULK INSERT in SQL. In this section, we'll look at what bulk insert means in SQL and how to perform a bulk import of the data in SQL.

If you want to insert multiple rows into the database table, then you can use BULK INSERT in SQL. According to the definition - "A BULK INSERT in SQL is a method or the process provided by the DBMS (Database Management System) to load multiple rows of the large data into the database table." Consider an example that a CSV file has 20,00,000 rows, and you want to import this file to a specific table in the SQL server, so you can easily use the BULK INSERT statement in the SQL server.

There are other ways to import the data in SQL, but the BULK INSERT statement in SQL is faster and provides several benefits that you'll study later in this topic.

Syntax

The below statement is a syntax of the bulk insert in SQL and it allows you to import the data or the files into a table or view in SQL. Let's look at the syntax to know how to insert bulk data in SQL.

Arguments

Now, let's analyze what each argument means in the syntax of BULK INSERT in SQL.

• table_name: Firstly, you have to specify a table name in the BULK INSERT clause of the SQL. The table_name could also be specified in the format as `database_name.schema_name.table_name.
• path_to_file: Here, the path of the files to be imported in the table needs to be specified.
• options: The WITH clause, as shown above, is used to add the options or the parameters that are used to define how the rows can be filtered and separated. The example of the BULK insert in SQL when used with the WITH clause is also explained below.

How to Insert Bulk Data in SQL?

Now, let's see how you can insert bulk data in SQL. To do this, you have to download a file with an enormous amount of data within it and load it into the SQL. For the sake of understanding, let's take the first 10 rows of the data.

To load this much amount of data, we also need a table. And to create a table, let's take a look at the below SQL code snippet:

Now, download a CSV file that has a large amount of data or just prepare a CSV file on your own with 3-5 records that have a location - D:\data\programs\salesperson.csv to understand the example.

Now, load the data from the CSV file to the sales table as shown below:

Let's decode the terms included in the above SQL code snippet. Using the data file present at the location D:\data\programs\salesperson.csv, we are inserting the data in bulk into the sales table. The WITH clause has different options - the FIELDTERMINATOR, here ',', is used to specify the separator between the columns. The ROWTERMINATOR, here '\n', is used to specify the new line between the rows. And the FIRSTROW=2 specifies the row starts at two and not one because we are not going to load the headers/column_names into the sales table.

This is how you can bulk insert in SQL using the BULK INSERT statement and WITH clause.

Examples For Bulk Insert in SQL

Now, let's take some examples to understand the bulk insert in SQL.

Example:

In this example, we'll be using the below Sales data. The below data is not huge and contains only 15 records but in reality, this data can be as huge as 10 lakh records or more.

Sales

The above records are stored in the CSV file named 10-records-sales.csv. Using this file we will load the data into the sales table.

Now, first, we'll create the table with the name sales, as shown in the below query.

The above SQL query creates a sales table with the column's heading. There is not a single piece of data present in the table as shown in the output below.

The below query is used to apply bulk insert in SQL.

The output of the above SQL query is shown below:

Learn More

• Advanced concepts of SQL.
• Online SQL Formatter