Open In App

BULK INSERT in SQL Server(T-SQL command)

Last Updated : 26 Jun, 2024
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Save
Share
Report
News Follow

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.

BULK INSERT  in SQL Server

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’

);



Similar Reads

Bulk Reading in Cassandra
In this article, we are going to describe how we can read in bulk which also helps in improve the performance. Before going to this article please understand the basic Architecture in Cassandra. We are going to creating data schema for exercise to test the bulk reading in Cassandra. Let's have a look. First, we are going to create table. Table Sche
2 min read
Capturing INSERT Timestamp in Table SQL Server
While inserting data in tables, sometimes we need to capture insert timestamp in the table. There is a very simple way that we could use to capture the timestamp of the inserted rows in the table. Capture the timestamp of the inserted rows in the table with DEFAULT constraint in SQL Server. Use DEFAULT constraint while creating the table: Syntax: C
2 min read
Insert Multiple Values Into Multiple Tables in SQL Server
To insert multiple values into multiple tables in SQL Server, use the OUTPUT clause. The SQL OUTPUT clause allows to insert multiple values into multiple tables in a single query. Output clause clause in SQL is used to capture data affected by Data Manipulation Language (DML) statements like INSERT, UPDATE, or DELETE. SyntaxSyntax to insert multipl
2 min read
Insert Statement in MS SQL Server
The SQL Server INSERT statement is a fundamental command used to add new rows of data to a table. Whether we are inserting specific values, utilizing default values or copying data from another table. In this guide, we’ll explore various ways to use the Insert statement in MS SQL Server with the help of various examples and so on. SQL Server INSERT
4 min read
Insert Into Select statement in MS SQL Server
The INSERT INTO SELECT statement in SQL Server is a versatile feature that enables you to efficiently copy data from one or more tables into another table. This functionality is essential for tasks such as data transfer, backup creation, and data merging. In this article, We will learn to Insert Into Select statement in MS SQL Server by understandi
4 min read
Configure SQL Jobs in SQL Server using T-SQL
In this article, we will learn how to configure SQL jobs in SQL Server using T-SQL. Also, we will discuss the parameters of SQL jobs in SQL Server using T-SQL in detail. Let's discuss it one by one. Introduction :SQL Server Agent is a component used for database task automation. For Example, If we need to perform index maintenance on Production ser
7 min read
SQL SERVER – Input and Output Parameter For Dynamic SQL
An Input Parameter can influence the subset of rows it returns from a select statement within it. A calling script can get the value of an output parameter. An aggregate function or any computational expression within the stored process can be used to determine the value of the output parameter. A parameter whose value is given into a stored proced
3 min read
SQL - SELECT from Multiple Tables with MS SQL Server
In SQL we can retrieve data from multiple tables also by using SELECT with multiple tables which actually results in CROSS JOIN of all the tables. The resulting table occurring from CROSS JOIN of two contains all the row combinations of the 2nd table which is a Cartesian product of tables. If we consider table1 contains m rows and table2 contains n
3 min read
How to Execute SQL Server Stored Procedure in SQL Developer?
A stored procedure is a set of (T-SQL ) statements needed in times when we are having the repetitive usage of the same query. When there is a need to use a large query multiple times we can create a stored procedure once and execute the same wherever needed instead of writing the whole query again. In this article let us see how to execute SQL Serv
2 min read
SQL Query to Convert Rows to Columns in SQL Server
In this article we will see, how to convert Rows to Column in SQL Server. In a table where many columns have the have same data for many entries in the table, it is advisable to convert the rows to column. This will help to reduce the table and make the table more readable. For example, Suppose we have a table given below: NAMECOLLEGEROLL NUMBERSUB
2 min read
How to SQL Select from Stored Procedure using SQL Server?
There may be situations in SQL Server where you need to use a stored procedure to get data from a SQL query. For direct data selection from a stored procedure within a query, SQL Server offers options like OPENQUERY and OPENROWSET. The usual way is running the stored procedure independently and then querying the outcomes. The idea of utilizing SQL
3 min read
SQL Server | Convert Tables in T-SQL into XML
XML (Extensible Markup Language) is a widely-used markup language designed to store and transfer structured data between different systems and platforms. While HTML focuses on the visual representation of data OverviewXML is similar to HTML which is designed to structure and store data for sharing across different systems and platforms.Unlike HTML,
3 min read
How to Insert Rows with NULL Values in SQL?
In SQL, due to lack of data, we sometimes need to insert rows with NULL values in the tables. Here, the keyword NULL(without quotes) is used to represent no data. There are some key points of Null value: NULL value is different from a zero value.A NULL value is used to represent a missing value, but that it usually has one of three different interp
2 min read
SQL Query to Insert Multiple Rows
Insertion in a table is a DML (Data manipulation language) operation in SQL. When we want to store data we need to insert the data into the database. We use the INSERT statement to insert the data into the database. In this article, we see how to insert individual as well as multiple rows in a database using the INSERT statement in the MSSQL server
2 min read
Performing Database Operations in Java | SQL CREATE, INSERT, UPDATE, DELETE and SELECT
In this article, we will be learning about how to do basic database operations using JDBC (Java Database Connectivity) API in Java programming language. These basic operations are INSERT, SELECT, UPDATE, and DELETE statements in SQL language. Although the target database system is Oracle Database, the same techniques can be applied to other databas
6 min read
SQL | INSERT INTO Query
The INSERT INTO statement of SQL is used to insert a new row in a table. There are two ways of using INSERT INTO statement for inserting rows: Only values: First method is to specify only the value of data to be inserted without the column names. Syntax: INSERT INTO table_name VALUES (value1, value2, value3,...); table_name: name of the table. valu
2 min read
SQL | INSERT IGNORE Statement
We know that a primary key of a table cannot be duplicated. For instance, the roll number of a student in the student table must always be distinct. Similarly, the EmployeeID is expected to be unique in an employee table. When we try to insert a tuple into a table where the primary key is repeated, it results in an error. However, with the INSERT I
2 min read
SQL INSERT INTO SELECT Statement
In SQL, the INSERT INTO statement is used to add or insert records to the specified table. We can use this statement to add data directly to the table. We use the VALUES keyword along with the INSERT INTO statement. VALUES keyword is accompanied by column names in a specific order in which we want to insert values in them. SELECT statement is used
5 min read
How to Insert a Line Break in a SQL VARCHAR
In SQL, VARCHAR and NVARCHAR are widely used data types for storing character data. It may sometimes be necessary to insert line breaks into these string values ​​for better readability or to display the information in a formatted manner. Although SQL itself does not have a specific newline character, there are methods to achieve this. In this arti
3 min read
How to fix Cannot Insert Explicit Value For Identity Column in Table in SQL
In SQL, the error message "Cannot insert explicit value for identity column in table 'table' when IDENTITY_INSERT is set to OFF" is a common issue encountered when attempting to insert explicit values into an identity column in a table. This error occurs because SQL, by default, does not allow explicit values to be inserted into identity columns un
7 min read
How to get the insert ID in SQL?
In the world of databases, having a clear understanding of primary keys is essential to maintaining data accuracy and ensuring efficient data retrieval. A primary key is a unique identifier for each record in a table and plays a critical role in database design. In SQL databases, obtaining the insert ID after adding a new record to a table is a com
4 min read
How to Insert Row If Not Exists in SQL
SQL's "Insert If Not Exists" feature acts as a security guard for your database, preventing duplicate entries that can cause errors and disrupt data analysis. This operation is crucial for data integrity and is commonly used in relational databases. SQL offers multiple ways to perform this operation, and we'll cover the "INSERT IF NOT EXISTS" opera
5 min read
How to Insert a Value that Contains an Apostrophe in SQL?
SQL is a standard database language used to access and manipulate data in databases. SQL stands for Structured Query Language. SQL was developed by IBM Computer Scientists in the 1970s. By executing queries SQL can create, update, delete, and retrieve data in databases like MySQL, Oracle, PostgreSQL, etc. Overall SQL is a query language that commun
3 min read
SQL INSERT INTO Statement
The INSERT INTO statement is a fundamental SQL command used to add new rows to a table in a database. It allows for the insertion of data either by specifying the column names along with the values or by providing values for all columns in the table. In this article, We will learn about SQL INSERT INTO Statement by understanding various examples an
8 min read
Difference between Structured Query Language (SQL) and Transact-SQL (T-SQL)
Structured Query Language (SQL): Structured Query Language (SQL) has a specific design motive for defining, accessing and changement of data. It is considered as non-procedural, In that case the important elements and its results are first specified without taking care of the how they are computed. It is implemented over the database which is drive
2 min read
SQL vs NO SQL vs NEW SQL
SQL stands for Structured Query Language. Which is based on relational algebra and schema is fixed in this which means data is stored in the form of columns and tables. SQL follows ACID properties which means Atomicity, Consistency, Isolation, and Durability are maintained. There are three types of languages present in SQL : Data Definition Languag
2 min read
Difference between ALTER and UPDATE Command in SQL
1. ALTER Command: ALTER SQL command is a DDL (Data Definition Language) statement. ALTER is used to update the structure of the table in the database (like add, delete, modify the attributes of the tables in the database). Syntax: // add a column to the existing table ALTER TABLE tableName ADD columnName columnDefinition; // drop a column from the
3 min read
SQL Query to Drop Unique Key Constraints Using ALTER Command
Here, we see how to drop unique constraints using alter command. ALTER is used to add, delete/drop or modify columns in the existing table. It is also used to add and drop various constraints on the existing table. Syntax : ALTER TABLE table_name DROP CONSTRAINT unique_constraint; For instance, consider the below table 'Employee'. Create a Table: C
2 min read
SQL Query to Add Unique key Constraints Using ALTER Command
Here we will see how to add unique key constraint to a column(s) of a MS SQL Server's database with the help of a SQL query using ALTER clause. For the demonstration purpose, we will be creating a demo table in a database called "geeks". Creating the Database : Use the below SQL statement to create a database called geeks: CREATE DATABASE geeks; Us
2 min read
How To Export and Import a .SQL File From Command Line With Options?
Structured Query Language is a computer language that we use to interact with a relational database.SQL is a tool for organizing, managing, and retrieving archived data from a computer database. In this article , we will learn to export and import .SQL files with command line options. Export:You can export the database along with its structure into
2 min read
Article Tags :
three90RightbarBannerImg