Bulk Data Insertion from CSV to SQL Server

Bulk Insertion is a process to insert massive data into database. There are several ways to insert bulk data from CSV files into database. This article explains how to insert bulk data into database,  how to insert CSV to SQL. Some common well known techniques to handle bulk data are given bellow:

• SQL BULK INSERT Query
• BCP or SqlBulkCopy library
• SQL Server Integration Service (SSIS)
• Normal SQL command library in C# or VB

SQL BULK INSERT Query
MS SQL Server provides a built in mechanism which is called SQL BULK INSERT statement. By using this we can easily insert massive data into the database directly from a CSV file. A sample BULK INSERT statement is:

BULK INSERT  MyRawData
FROM 'D:\test.csv'
WITH
(
	 FIRSTROW = 2
	,FIELDTERMINATOR = ','
	,ROWTERMINATOR = '\n'
)

The above SQL script will insert data from a CSV file into a table named “MyRawData”. The name of the CSV file is “test.csv” and its location is D drive. We can set the file name or directory location based on our needs.

BCP or SqlBulkCopy Library
Different programing language provides own mechanism to handle massive data. Microsoft C Sharp (C#) offer a technology (SqlBulkCopy) which can be used to insert bulk data into database. Sample C# code is given bellow:

const string CSV_CONNECTIONSTRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"{0}\";Extended Properties=\"text;HDR=YES;FMT=Delimited\"";

string CSVpath = "C:\\"; // CSV file Path

var AllFiles = new DirectoryInfo(CSVpath).GetFiles("*.CSV");
string File_Name = string.Empty;
string ConStr = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
//ConnectionString

for (int i = 0; i < AllFiles.Length; i++)
{
    try
    {
        File_Name = AllFiles[i].Name;
        DataTable dt = new DataTable();

        using (OleDbConnection con = new OleDbConnection(string.Format(CSV_CONNECTIONSTRING, CSVpath)))
        {
            using (OleDbDataAdapter da = new OleDbDataAdapter("select * from [" + File_Name + "]", con))
            {
                da.Fill(dt);
            }
        }

        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(ConStr))
        {
            bulkCopy.ColumnMappings.Add(0, "Coloumn1");
            bulkCopy.ColumnMappings.Add(1, "Coloumn2");
            bulkCopy.ColumnMappings.Add(2, "Coloumn3");
            bulkCopy.DestinationTableName = "MyTableName";
            bulkCopy.BatchSize = dt.Rows.Count;
            bulkCopy.WriteToServer(dt);
            bulkCopy.Close();
        }

    }
    catch (Exception ex)
    {
        throw ex;
    }
}

The above C# codes will insert the data from a C# file into table “MyTableName”. Here, “MyTableName”  table has only three columns, but we can change it based on our demands. Remember that this code only process those CSV file those have three columns. That means we need to consider the number of column of CSV file.

SQL Server Integration Service (SSIS)
SQL Server includes a powerful data integration and transformation applications called SQL Server Integration Service (SSIS). Its main functionality is to move data from external data source into SQL Server. Using Business Intelligent Development Studio (BIDS) we can easily import data from a CSV file into database. Also, it is very simple to put a package file in as automatic reoccurring job. Here are the basic steps to create a SSIS service package to import data from a CSV file into SQL Server.

• Open SQL Server Business Intelligence Studio.
• Create a new “Integration Service Project”.
• In the “Control Flow” tab, drag a “Data Flow Task” from the toolbox.
• Go to “Data Flow” tab.
• In the “Data Flow” page, drag “Flat File Source” and “ADO.NET Destination” from the toolbox and set them up.
• Connect Flat File Source output path (green arrow) to the ADO.NET Destination.

Now run SSIS package. It will insert data from the CSV file to the SQL database.

During this insertion time we should consider the processing speed and time. It it takes long time then we have to choose different process (mentioned in the above) or check the CSV file.