MS SQL Server

SQL Server Bulk Insert

As the name suggests, bulk insert refers to a technique of inserting a large amount of data quickly from a text or CSV file into a SQL Server table or view. It is a very useful feature when performing restore from backup, as it only requires you to deal with the bare minimum configuration. Let us discuss how we can perform bulk insert in SQL Server.

Requirements

To follow along with this article, you will need:

  1. SQL Server instance.
  2. Sample CSV or text file.

For illustration, we have a CSV file containing 1000 records. You can download a sample file in the link below:

https://www.dropbox.com/s/n37lsu99chh5dr6/bulk_insert_sample.csv?dl=1

Step 1: Create Database

The first step is to create a database in which to import the CSV file. For our example, we will call the database.

bulk_insert_db.

We can a query as:

create database bulk_insert_db;

Once we have the database setup, we can proceed and insert the required data.

Import CSV File Using SQL Server Management Studio

We can import the CSV file into the database using the SSMS import wizard. Open the SQL Server management Studio and login to your server instance.

On the left-hand pane, select your database and right click.

Navigate to Task -> Import Flat File.

This will launch the import wizard and allow you to import your CSV file into your database.

Click Next to proceed to next step. In the next part, select the location of your CSV file, set your table name and select the schema.

You can leave the schema option as default.

Click on Next to preview the data. Make sure the data is as provided by selected CSV file.

The next step will allow you to modify various aspects of the table columns. For our example, let us set the id column as the primary key and allow null in the Country column.

With everything set, click Finish to start the import process. You will get success if the data has been imported successfully.

To confirm the data is inserted into the database, query the database as:

use bulk_insert_db;

select top 10 * from bulk_insert_sample;

This should return the first 10 records from the csv file.

Bulk Insert Using T-SQL

In some instances, you do not get access to a GUI interface for importing and exporting data. Hence, it is important to learn how we can perform the above operation purely out of SQL queries.

The first step is to setup the database. For this one, we can call it bulk_insert_db_copy:

create database bulk_insert_db_copy;

This should return:

Commands completed successfully.

Completion time: <>

The next step is to set up our database schema. We will refer to the CSV file to determine how to create our table.

Assuming we have a CSV file with the headers as:

We can model the table as shown:

create table bulk_insert_table (

id int primary key not null identity(100,1),

firstname varchar(50) not null,

lastname varchar(50) not null,

email varchar(255) not null,

country varchar(50),

profession varchar(50)

);

Here, we create a table with the columns as the headers of the csv.

NOTE: Since the id value starts at a100 and increases by 1, we use the identity(100,1) property.

Learn more here: https://linuxhint.com/reset-identity-column-sql-server/

The last step is to insert the data. An example query is as shown below:

bulk insert bulk_insert_table

from '<path to csv file>'

with (firstrow = 2,

fieldterminator = ',',

rowterminator = '\n'

);

Here, we use the bulk insert query followed by the name of the table to which we wish to insert the data. Next is the from statement followed by the path to the CSV file.

Finally, we use the with clause to specify import properties. The first is firstrow which tells SQL server that the data starts at row 2. This is useful if your CSV file contains data header.

The second part is fieldterminator which specifies the delimiter for your CSV file. Keep in mind that there is no standard for CSV files, hence it can include other delimiters such as spaces, periods, etc.

The third part is rowterminator which describes one record in the CSV file. In our case one line = one record.

Running the code above should return:

(1000 rows affected)

Completion time:

You can verify the data exists by running the query:

select top 10 * from bulk_insert_table;

This should return:

And with that, you have successfully, inserted a bulk CSV file to your SQL Server database.

Conclusion

This guide explores how to bulk insert data into a SQL Server database table or view. Check out our other great tutorial on SQL Server:

https://linuxhint.com/category/ms-sql-server/

Happy SQL!!!

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list