In this article, you will learn how to insert more than a million records into the SQL server database table. Also, you will understand how to use the SQL bulk insert command, and import a big file of data using c# and procedure – just pay attention during the reading.
Table Of Contents
Basically, SQL Server bulk insert is a command that can be executed in the SQL server – And it is giving you the ability to send a full file to the database table.
Prerequisites
- Some knowledge of SQL query commands
- Some knowledge of C# or any other .NET programming language
- SQL Server Database
- SQL Server Management Studio
Before we discuss, how to use the SQL server bulk insert – We just need to know what is the difference between the following.
- SQL
- SQL Server
- MySQL
The Differences Between SQL, SQL Server, MySql
SQL
SQL refers to Structured Query Language that can be used to interact with the databases –
The common uses for the SQL language are to retrieve data from the database, delete rows from the database, and insert, and update records using the SQL language.
So we can say, it is a query language for dealing and interacting with the database. There are some database management systems that are using the SQL query in their products such as Microsoft SQL Server, Oracle, Ingres, and so many others.
The following shows you examples for the SQL Query.
As I mentioned before there are some commands to interact with the database using SQL language.
The first one is the retrieve command.
SELECT name, price, quantity FROM products;To insert data using the SQL language.
INSERT INTO products (name, price, quantity) VALUES ('T-Shirt', '$55.00', 5);To delete records using the SQL Language.
DELETE from products WHERE name='T-Shirt';Also, we can use another query to update the data in the database using SQL language.
UPDATE products SET quantity='', price='' WHERE name='T-Shirt';As you saw the previous commonly SQL commands that already interact with the database. In the next few lines, we are going to explain what are MySQL and SQL Server.
MySQL and SQL Server
Basically, MySQL and SQL Server are database management systems that already use the SQL language inside their platform.
MySQL and SQL Server are helping developers to see the database table in GUI.
And also allow them to run the SQL commands inside their platforms.
That’s all, so the main thing is SQL language without it – The SQL Server and MySQL will not work. And that is happening because they are depending on.
On other hand, Let’s discuss one of the SQL statements in the SQL Server Management Studio – The SQL Server Bulk Insert.
SQL Server Bulk Insert Syntax
Actually, the SQL bulk insert is used to store the data from external files in the database table. And also used to view and import data from Azure blob storage. or any disk else you just need to specify a valid path for the importable file.
The command consists of many arguments and options like the below.
BULK INSERT [table_name] FROM [file_path] WITH [options]As you saw in the previous command there are many arguments added. table_name, file_path, and options. let’s take a look at each one.
- table_name refers to the database table name and it can be written with the table name directly or the full name started from the database name and ended with the table name like this database_name.schema_name.table_name.
- file_path refers to a path for the data file and it should be a valid path without any permissions.
- options refer to all options that are running through executing the command.
Let’s dive more into SQL Bulk insert Options.
SQL Server Bulk Insert Important Options
In this section, we expose some of the important options with the SQL server bulk insert command. When you set the file path you have to define which is the extension name of this file.
Format Option
The file extension can be specified with ” FORMAT ” option to define a Comma-Separated Values ( CSV ). It can be like below.
BULK INSERT book FROM 'D:\\book_data.csv' WITH (
FORMAT = 'CSV'
);File Format
It specifies the file format. the below list shows you, Some of the file formats that are allowed with SQL bulk insert.
- Comma-Separated Values ( CSV )
- XML
Collecting Errors
Naturally, when we try to insert any data into a database we need to ensure from this data is already inserted or not. And sometimes there are some errors are happening behind the scene. How can we know and expose these errors?
Microsoft already made an option inside SQL bulk insert to collect all rows that have errors or a datatype formatting issue in the same directory for the CSV File. the property is called ” ERRORFILE “.
So with the same previous SQL code, we can specify the error file path inside the SQL command like the below code.
BULK INSERT book FROM 'D:\\book_data.csv' WITH (
FORMAT = 'CSV',
ERRORFILE = 'D:\\database_errors.txt'
);Separate The CSV File into Small Batch Size
If you didn’t specify the batch size the operation system of SQL transaction will store the full CSV file in one batch, however, if you can specify “BATCHSIZE” property will separate the processing into a number of small batches.
BULK INSERT books FROM 'D:\\small_file.csv' WITH (
FORMAT = 'CSV',
BATCHSIZE = 3
);The process is inserting the file records every 3 rows. This means it inserts 3 rows then start to insert another 3 rows and so on until finishes all rows that are inside the CSV file.
With the previous SQL command, the result should be like the below.
If you need to know more details about the other options you have to navigate to this link.
SQL Bulk Insert Example
We have a CSV file with big data for the annual enterprise survey generated in the 2020 financial year and need to insert it into our database table.
But before that, we need to check first what is the data type for each column. The file looks like the below image.
You can download it from here.
Firstly, need to create a table with the same column in the survey file and do the same data type for columns.
-- PRIMARY KEY
CREATE TABLE Financial_Survey (
year INT NULL,
account_name VARCHAR(50) NULL,
units VARCHAR(50) NULL,
amounts VARCHAR(50) NULL
);Then, We have to put the financial survey file on the local desk D:\ then open “New Query” in SQL server management studio and write the below SQL command.
We will only specify the error file path in the command to check the errors.
BULK INSERT Financial_Survey FROM 'D:\\Financial_Survey.csv' WITH (
FORMAT = 'CSV',
ERRORFILE = 'D:\\Database_Errors.txt'
);The following result shows you an initialized file of errors.
You will see this file in the same directory of the Financial_Survey.csv file path and these errors occurred because we inserted it in the wrong data type columns.
Look at the amounts column it already has an integer data type but the file had a number with a separated comma and wrong formatted data.
Fixing File Issues
Let’s correct the unformatted data and the wrong data type.
Delete the commas “,” from amounts column values in the CSV file by selecting the full column and right click then choose “numbers” from “Cell Formatting” and delete the comma by unchecking use comma option otherwise you have to select “(1234)” as an integer data type.
In the next step, you have to Change the varchar(50) to varchar(max) like the below code to avoid any errors else.
Also, make sure the columns in the CSV file should be in the same order as in the database table columns.
Anyway, the updated CSV file should be here you can redownload it again.
-- PRIMARY KEY
CREATE TABLE Financial_Survey (
year INT NULL,
account_name VARCHAR(max) NULL,
units VARCHAR(max) NULL,
amounts INT NULL
);Then try to execute the same command of SQL bulk insert data.
The result should appear like the below image when you use the select query.
Storing and Importing CSV File Using C# into Database Table
It is easy to import CSV files or create a bulk insert using the stored procedure and C#. With the SQL bulk insert command, you have to create a procedure in the SQL server with a required parameter containing the path of the CSV file. That’s all
Note: you couldn’t able to use the same previous query in the procedure, we have to do some changes.
CREATE PROC Financial_Year_Proc
@fpath nvarchar(500)
AS
DECLARE @bulk_query NVARCHAR(2000)
SET @bulk_query = N'BULK INSERT Financial_Survey FROM ''' +
@fpath +
N''' WITH (FORMAT= ''CSV'')';
EXEC sp_executesql @bulk_query;In the next step, we have to create a database connection with C# and then develop a file explorer with only CSV type. the below code is only some snippets from our real project to just focus on the idea.
Open visual studio code and create a new c# desktop project.
The form should be like the below image.
Let’s create the database connection.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace WindowsFormsApp1
{
public partial class Form1 : Form
{
string con_string;
SqlConnection cnn;
public Form1()
{
this.con_string = @"Data Source=.\SQLEXPRESS;Initial Catalog=dbtest; Integrated Security =true";
this.cnn = new SqlConnection(this.con_string);
InitializeComponent();
}
// Button of file explorer should be here.
}
}
Then we have to create an event for the “Choose The Path” button and inside it, we would do an invocation for the “OpenFileDialog” that allows us to do files explorer then we have to do only CSV file type on the filter to show only CSV files in the explorer.
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog opnfd = new OpenFileDialog();
opnfd.Filter = "Excel Files (*.csv;)|*.csv;";
if (opnfd.ShowDialog() == DialogResult.OK)
{
// Here we need to build
string file_path = opnfd.FileName;
this.execute_proc(file_path);
}
}After that, let’s create the “execute_proc” function that will be responsible for sending the file path to the stored procedure.
public void execute_proc(string file_path ) {
this.cnn.Open();
SqlCommand sqlcmd = new SqlCommand();
sqlcmd.CommandType = CommandType.StoredProcedure;
//Path The name of Procedure
sqlcmd.CommandText = "Financial_Year_Proc";
sqlcmd.Connection = cnn;
// Procedure Parameters
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@fpath", SqlDbType.VarChar);
param[0].Value = file_path;
sqlcmd.Parameters.AddRange(param);
sqlcmd.ExecuteNonQuery();
this.cnn.Close();
}Conclusion
SQL Server bulk insert is a command used in a procedure, or SQL server query to store big file data. The order of columns, column counts, and data types in the CSV file should be the same in the database table to avoid any errors.
The SQL Server bulk insert has some changes when we use it in a procedure. And there are many options to help us such as collecting errors from table rows, file formatting, and some other settings you can see more details through this link.
If you have any problem with your code please don’t hesitate to contact us. We are here to help you. Have a good day 🙂