Bulk INSERT in MS SQL Server

Sometimes you need to insert multiple rows into your desired table in MS SQL Server. A Bulk insert is a process or method provided by a database management system to load multiple rows of data into a table. In this tutorial I will show you different way of bulk insert into table in MS SQL Server..

Let’s create a table first to demonstrate the bulk INSERT operation:

CREATE TABLE [DataAnalytics].[dbo][tblt_sample_bulk_insert]
(
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL
);

If you want to learn more about CREATE TABLE statement read my another article CREATE TABLE Statement in MS SQL Server.

Now, let’s think you have to bulk insert the following data into the above table:

Method-1:
In this method I’ll use direct INSERT statement. If the amount of data is not large enough you can use this method. Generally it is a slow process and you have to prepare INSERT statement for each row of the bulk data. You need to take care about special character in this case. Now, let’s see an example:

INSERT INTO [DataAnalytics].[dbo][tblt_sample_bulk_insert]
(id,name,email) VALUES(1,'Fahim','fahim@gmail.com');
INSERT INTO [DataAnalytics].[dbo][tblt_sample_bulk_insert]
(id,name,email) VALUES(2,'Karim','karim@gmail.com'), 
INSERT INTO [DataAnalytics].[dbo][tblt_sample_bulk_insert]
(id,name,email) VALUES(3,'Rahim','rahim@gmail.com'), 
INSERT INTO [DataAnalytics].[dbo][tblt_sample_bulk_insert]
(id,name,email) VALUES(4,'Hakim','hakim@gmail.com');
INSERT INTO [DataAnalytics].[dbo][tblt_sample_bulk_insert]
(id,name,email) VALUES(5,'Minhaj','freetechtrainer@gmail.com');

Method-2:
It is similar to the method-1 but in this case you don’t need to write INSERT INTO table_name (column names) VALUES for every rows. It is faster than the above method but there is a limitation in this case. You can not add more that 1,0000 rows in this method. Now, let’s see an example:

INSERT INTO [DataAnalytics].[dbo][tblt_sample_bulk_insert]
(id,name,email) VALUES
(1,'Fahim','fahim@gmail.com'), 
(2,'Karim','karim@gmail.com'), 
(3,'Rahim','rahim@gmail.com'), 
(4,'Hakim','hakim@gmail.com'),
(5,'Minhaj','freetechtrainer@gmail.com');

Method-3:

Now I want to talk about the most efficient method of bulk insert. The following BULK INSERT statement imports the  file to the tblt_sample_bulk_insert table.

BULK INSERT [DataAnalytics].[dbo][tblt_sample_bulk_insert]
FROM 'C:\tblt_sample_bulk_insert.txt'
WITH (
      FIRSTROW = 2,
      FIELDTERMINATOR = '\t',
      ROWTERMINATOR='\n' ,
      MAXERRORS=2
);

In the above syntax:

• The FIRSTROW parameter specifies the starting point of the insert statement. In the below example, we want to skip column headers so we set this parameter to 2.
• FIELDTERMINATOR defines the character which separates fields from each other. SQL Server detects each field in such a way. In this example we see it is tab (\t) delimited. But it can be comma delimited or others delimiter characters. But how can we detect a fieldterminator? Open the txt file in Notepad++ and then navigate to View->Show Symbol->Show All Characters, then you will find the used delimited character.
• ROWTERMINATOR defines the separation character of rows. Generally CRLF (\n) characters is used at the end of each row. But you can check it by the above mentioned method.
• MAXERRORS parameter specifies the maximum acceptable error in the operation

BULK INSERT [DataAnalytics].[dbo][tblt_sample_bulk_insert]
FROM 'C:\tblt_sample_bulk_insert.txt'
WITH (
      FIRSTROW = 2,
      FIELDTERMINATOR = '\t',
      ROWTERMINATOR='\n' ,
      BATCHSIZE=100000
);

In the above syntax:

• The BATCHSIZE parameter divide the file into batches according to batch size i.e. if there are 1 lac  rows in a file and BATCHSIZE=10000 then rows will be uploaded into 10 batches.

BULK INSERT [DataAnalytics].[dbo][tblt_sample_bulk_insert]
FROM 'C:\tblt_sample_bulk_insert.txt'
WITH (
      FIRSTROW = 2,
      FIELDTERMINATOR = '\t',
      ROWTERMINATOR='\n' ,
      MAXERRORS=2,
      BATCHSIZE=100000
);

In the above syntax:

• In addition, if you use both MAXERRORS and MAXERRORS parameters at the same time, the bulk copy process will not cancel the whole insert operation for a specific batch error, it will only cancel the specific batch part.

In the bulk operation you need to remember  below few things:

• If your data table has more columns than text file, create a view of the table with the fields similar with the text file and use view name instead of table name in the BULK INSERT statement
• You need to have access of the BULK INSERT
• You have to keep the file into server or other place where server has access that location to access the file
• Field delimiter should not be used in the field value

In this tutorial, I have shown you how to bulk insert in SQL Server using INSERT INTO statement and BULK INSERT statement. Hope you have enjoyed the tutorial. If you want to get updated, like my facebook page http://www.facebook.com/freetechtrainer and stay connected.