Pages

Jul 6, 2016

Efficient | fast way to populate large volume of data in a table c# SQL Server

Sometimes there is a need to populate | insert a large volume of data from application code(c#, Java, vb.net) to a table in the database (SQL Server). If data volume is less then performance is not a big concern. But if the data volume is big we must have to consider the most efficient approach. Let us discuss different approaches and their advantages and disadvantages:

Looping over insert statement:

One of the easiest ways to write an insert statement inside a loop. And it will populate records one by one in the database. For example:

foreach (DataRow row in table.Rows) {

using (SqlConnection connection = new SqlConnection(connectionString))

using (SqlConnection connection = new SqlConnection(connectionString))

{

SqlCommand cmd = new SqlCommand(Query);

cmd.CommandType = CommandType.Text;

cmd.Connection = connection;

connection.Open();

cmd.ExecuteNonQuery();

}

In the above approach database is called only one time but inside database rows are being inserted one by one. So it is rows based approach and it is not efficient in case of a large volume of data.

Table value constructor:

In SQL server table value constructor is set based approach. And it is very efficient. Instead of the full insert statement, we can save the query string in table value constructor format. For example:

string Query = @"INSERT INTO Users(Name, Phone, Age)

VALUES ('Name_1', 'Phone_1', 'Age_1')

VALUES ('Name_2', 'Phone_2', 'Age_2')

.....

.....

VALUES ('Name_n', 'Phone_n', 'Age_n')"

Although this approach is very efficient but limited to 1000 rows. If there are more than 1000 rows above approach is not going to work.

Passing Data table:

One of the best approaches is passing the data table as a parameter to stored procedure. For example:

Create a table type in SQL server:

CREATETYPE UserType ASTABLE(

Name VARCHAR(50)NOTNULL,

Phone VARCHAR(10),

Age INT

)

Create a stored procedure which accepts UserType as a parameter:

CREATEPROCEDURE AddUsers(

@UserTable UserType READONLY

)

AS

BEGIN

INSERTINTO Users

SELECT*FROM @UserTable

END

Using C# code, execute stored procedure and pass data table as a parameter:

using (SqlConnection conn = new SqlConnection(conn))

using (SqlCommand cmd = new SqlCommand("dbo.AddUsers", conn))

{

var dt = new DataTable();

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@UserType", dt));

conn.Open();

cmd.ExecuteNonQuery();

}

Using bulk insert:

SQL server support bulk inserts that loading the data from a file. For this first, we have to download or copy data file at the specified location using programming language. Then using BULK INSERT command data can be imported into a table. For example:

BULKINSERT EXACT_HELP.dbo.Users

FROM'C:\Data\Users.txt'

WITH

(

FIELDTERMINATOR=' |',

ROWTERMINATOR=' |\n'

);

We can also do by using C# SqlBulkCopy.

Pass data in the XML format in SQL server:

In your programming language, first of all, covert whole data table in XML format. Pass that XML to a stored procedure to populate the table. For example: