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))

    {

        SqlCommand cmd = new SqlCommand("INSERT INTO Users(Name, Phone, Age) VALUES (@Name, @Phone, @Age)");

        cmd.CommandType = CommandType.Text;

        cmd.Connection = connection;

        cmd.Parameters.AddWithValue("@Name", row[1]);

        cmd.Parameters.AddWithValue("@Phone", row[2]);

        cmd.Parameters.AddWithValue("@Age", row[3]);

        connection.Open();

        cmd.ExecuteNonQuery();

    }

}

      

Here the problem with above script is there is separate database call for each row.  For a large volume of records above script will take a significant amount of time.

Multiple insert statement in a single query:

In this approach first all insert queries are saved in a string variable. Then whole insert statement is executed in the database at the same time. For example:

string Query = @"INSERT INTO Users(Name, Phone, Age) VALUES ('Name_1', 'Phone_1', 'Age_1')

INSERT INTO Users(Name, Phone, Age) VALUES ('Name_2', 'Phone_2', 'Age_2')

.....

.....

INSERT INTO Users(Name, Phone, Age) VALUES ('Name_n', 'Phone_n', 'Age_n')"

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:

CREATE TYPE UserType AS TABLE(

      Name VARCHAR(50) NOT NULL,

      Phone VARCHAR(10),

      Age INT

)

Create a stored procedure which accepts UserType as a parameter:

CREATE PROCEDURE AddUsers(

    @UserTable UserType READONLY

)

AS

BEGIN

    INSERT INTO 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:

BULK INSERT 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:

Create a stored procedure:

CREATE PROCEDURE AddUers(

    @UserTable XML

)

AS

BEGIN

      DECLARE @idoc int

      EXEC sp_xml_preparedocument @idoc OUTPUT, @UserTable;

    INSERT INTO Users

      SELECT  * 

      FROM OPENXML (@idoc, '/ROOT/Users',1) 

    WITH (

            Name VARCHAR(50),

            Phone VARCHAR(10),

            Age INT)

END

C# code to pass XML data:

                  

DataSet ds = new DataSet();

ds.Tables.Add(dt1);

string UserXml= ds.GetXml();

using (SqlConnection conn = new SqlConnection(conn))

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

{

    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add("@UserTable", SqlDbType.Xml);   

    cmd.Parameters["@UserTable"].Value = UserXml;        

    conn.Open();

    cmd.ExecuteNonQuery();

         

}