Bulk Insert in SQL Server

Using SQL Server BULK INSERT(BCP) statement you can perform large imports of data from text, or Csv files to SQL Server table, or Views. Basically, to perform BULK INSERT, you need a Source (.CSV, .txt  File) and a Target (SQL table, view).

Lets see, how to use BULK INSERT statement to Load data from CSV Files to SQL Server Table.

Here we have a .CSV format file named as TransactionHistory.csv, which contains 113444 rows  and this is our source file.

Source file is placed at path – C:\Users\prade\Desktop\Data\TransactionHistory.csv, this path we will be used later in BULK INSERT statement.



Now we need to create a Target that is SQL table to load this data.

Lets create a table in SQL Server, and give it name as TranHistory.

CREATE TABLE TranHistory (TransactionID INT, 
ProductID INT, 
ReferenceOrderID INT, 
ReferecnceOrderLineID INT, 
TransactionDate Datetime,
TransactionType CHAR(1), 
Quantity Numeric(9,2), 
ActualCost Numeric(9,2), 
ModifiedDate Datetime)


Now we have a table in SQL Server as you can see in below screenshot.


To Load the file’s (Source) data To SQL table (Target), write a following BULK INSERT Statement, and execute it.

FROM 'C:\Users\prade\Desktop\Data\TransactionHistory.csv'

Lets understand the syntax that is used in BULK INSERT Statement.

As a first argument to BULK INSERT, is Target name that is SQL table TranHistory. It means we want bulk import data into this table. Instead of table name you can also use view.

Next, provided a file path as second argument that is Source to BULK INSERT. It means this is the full path of the data file that contains data to import into the specified table or view.

After that specified a value 2 for option FIRSTROW  that is FIRSTROW =2, that means we want to skip first row from source file. As first row contains column header that we do not want to load in SQL table.

Next specified the file format, that depends upon the type of the file, you may want to specify the row delimiter and column delimiter.

As we have a comma separated file so provided a FIELDTERMINATOR as ‘,’ and ROWTERMINATOR as ‘\n’ that is the default row terminator.


You can see, after executing BULK INSERT statement, a message says 112444 rows affected that means BULK INSERT statement executed successfully.

Lets verify, select records from target that is our SQL table – TranHistory, also counts the number of rows that table does have.

You can see, table has same data, and rows count.

SELECT * FROM TranHistory
SELECT COUNT(*) AS TotalRows FROM TranHistory

count records in table

Load Data in Batches Using BULK INSERT

You can also perform Bulk Insert in form of Batches by specifying a BATCHSIZE in BULK INSERT statement for the number of rows in a batch.

Each batch is copied to the server as one transaction so in case of a failure everything does not get rolled back. By default, all data in the specified data file is one batch.

Lets modify the above BULK INSERT statement and specify batch size =40000, means data will be loaded in increment of 40000.

You can see message, data is loaded in batches that is increment of 40000.

bulk insert


Also Read..

Import file in SQL Server

SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window

 348 total views,  6 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.