BULK INSERT in SQL Server(T-SQL command)

BULK INSERT in SQL Server(T-SQL command): In this article, we will cover bulk insert data from csv file using the T-SQL command in the SQL server and the way it is more useful and more convenient to perform such kind of operations. Let’s discuss it one by one. 

Condition

Sometimes there is a scenario when we have to perform bulk insert data from .csv files into SQL Server database. We can use the GUI interface in SSMS(SQL Server Management Studio) to import data from Excel, CSV, etc files.

What if we have millions of data to be imported?

The above will be a time-consuming task so, now you will see how you can handle such kinds of operations.

SQL Server provides the BULK INSERT statement to perform large imports of data into SQL Server using T-SQL.

Note – Requires INSERT and ADMINISTER BULK OPERATIONS permissions.

Approach

Here, we will provide a faster alternative to the above via few lines of BULK INSERT in SQL Server T-SQL Command. 

Genera Syntax for BULK INSERT in SQL Server(T-SQL command)

BULK INSERT <DATABASE NAME>.<SCHEMA NAME>.<TABLE_NAME>
FROM '<FILE_PATH>'
WITH
(
-- input file format options
   [ [ , ] FORMAT = 'CSV' ]
   [ [ , ] FIELDQUOTE = 'quote_characters']
   [ [ , ] FORMATFILE = 'format_file_path' ]
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ]
   [ [ , ] ROWTERMINATOR = 'row_terminator' ]
    )]
)

The most commonly used input parameters are the following as follows.

1. FIELDTERMINATOR –‘
   ‘field_terminator’ is used as a separator between fields. The default field terminator is \t (tab character).
    
2. ROWTERMINATOR –
   ‘row_terminator’ is used to specify the end of the row. The default row terminator is \r\n (newline character). 

Importing CSV file into SQL table

Here, we will discuss importing CSV file into a table in SQL server. Consider the given below CSV file or you can use your own csv file to perform this operation. Let us try to import a csv file into our SQL table in SQL Server.

Now, let’s create the SQL table to store the above data.

Creating table

Here we will create the table schema as per csv file records.

USE [Test]--Database name

CREATE TABLE [dbo].[Employee](                              
[Emp ID] bigint primary key,
[First Name] [varchar](50) NULL,
[Last Name] [varchar](50) NULL,
[Salary] bigint,
[City] [varchar](50)
)

Bulk Inserting

Let’s now bulk insert the file into the Employee SQL table :

BULK INSERT [Test].[dbo].[Employee]
FROM 'C:\data\employee_data.csv'
WITH (FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR='\n' );

Output –
After executing the above operation we can see the records have been inserted with few lines of code into the Employee table like below.

References :
To get more info on the input format options, visit https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-2017#input-file-format-options.

BULK INSERT in SQL Server(T-SQL command)- FAQs

How Do I Insert Data in Bulk?

To insert data in bulk in SQL, you can use a INSERT INTO statement with multiple rows:

INSERT INTO table_name (column1, column2)

VALUES

(value1, value2),

(value3, value4),

(value5, value6);

Why Use Bulk Inserts?

Efficiency: Reduces the number of database calls, improving performance.

Speed: Faster than inserting rows one at a time, especially for large datasets.

Resource Usage: Lowers network and server resource consumption.

What is Batch Insert in SQL?

A batch insert involves grouping multiple INSERT statements into a single batch to be executed together:

BEGIN;

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

INSERT INTO table_name (column1, column2) VALUES (value3, value4);

INSERT INTO table_name (column1, column2) VALUES (value5, value6);

COMMIT;

What is Bulk Load in SQL?

Bulk load is a method to quickly import large amounts of data into a database from an external source, often using specialized commands or tools.

Example Command: In SQL Server, BULK INSERT is used:

BULK INSERT table_name

FROM ‘file_path’

WITH (

FIELDTERMINATOR = ‘,’,

ROWTERMINATOR = ‘\n’

);

S

Sruti Rai

Follow

Improve