SQL Insert one row or multiple rows data?

Question

I am working on a console application to insert data to a MS SQL Server 2005 database. I have a list of objects to be inserted. Here I use Employee class as example:

List<Employee> employees;

What I can do is to insert one object at time like this:

foreach (Employee item in employees)
{
  string sql = @"INSERT INTO Mytable (id, name, salary) 
    values ('@id', '@name', '@salary')";
  // replace @par with values
  cmd.CommandText = sql; // cmd is IDbCommand
  cmd.ExecuteNonQuery();
}

Or I can build a balk insert query like this:

string sql = @"INSERT INTO MyTable (id, name, salary) ";
int count = employees.Count;
int index = 0;
foreach (Employee item in employees)
{
   sql  = sql + string.format(
     "SELECT {0}, '{1}', {2} ",
     item.ID, item.Name, item.Salary);
   if ( index != (count-1) )
      sql = sql + " UNION ALL ";
   index++
 }
 cmd.CommandType = sql;
 cmd.ExecuteNonQuery();

I guess the later case is going to insert rows of data at once. However, if I have several ks of data, is there any limit for SQL query string?

I am not sure if one insert with multiple rows is better than one insert with one row of data, in terms of performance?

Any suggestions to do it in a better way?

Solution 1

Actually, the way you have it written, your first option will be faster.

1. Your second example has a problem in it. You are doing sql = + sql + etc. This is going to cause a new string object to be created for each iteration of the loop. (Check out the StringBuilder class). Technically, you are going to be creating a new string object in the first instance too, but the difference is that it doesn't have to copy all the information from the previous string option over.

2. The way you have it set up, SQL Server is going to have to potentially evaluate a massive query when you finally send it which is definitely going to take some time to figure out what it is supposed to do. I should state, this is dependent on how large the number of inserts you need to do. If n is small, you are probably going to be ok, but as it grows your problem will only get worse.

Bulk inserts are faster than individual ones due to how SQL server handles batch transactions. If you are going to insert data from C# you should take the first approach and wrap say every 500 inserts into a transaction and commit it, then do the next 500 and so on. This also has the advantage that if a batch fails, you can trap those and figure out what went wrong and re-insert just those. There are other ways to do it, but that would definately be an improvement over the two examples provided.

var iCounter = 0;
foreach (Employee item in employees)
{

   if (iCounter == 0)
  {
    cmd.BeginTransaction;
  }
  string sql = @"INSERT INTO Mytable (id, name, salary) 
    values ('@id', '@name', '@salary')";
  // replace @par with values
  cmd.CommandText = sql; // cmd is IDbCommand
  cmd.ExecuteNonQuery();
  iCounter ++;
  if(iCounter >= 500)
  {
     cmd.CommitTransaction;
     iCounter = 0;
  }
}

if(iCounter > 0)
   cmd.CommitTransaction;

Solution 2

In MS SQL Server 2008 you can create .Net table-UDT that will contain your table

CREATE TYPE MyUdt AS TABLE (Id int, Name nvarchar(50), salary int)

then, you can use this UDT in your stored procedures and your с#-code to batch-inserts. SP:

CREATE PROCEDURE uspInsert
 (@MyTvp AS MyTable READONLY)
AS
     INSERT INTO [MyTable]
      SELECT * FROM @MyTvp

C# (imagine that records you need to insert already contained in Table "MyTable" of DataSet ds):

using(conn)
{
    SqlCommand cmd = new SqlCommand("uspInsert", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    SqlParameter myParam = cmd.Parameters.AddWithValue
     ("@MyTvp", ds.Tables["MyTable"]);

    myParam.SqlDbType = SqlDbType.Structured;
    myParam.TypeName = "dbo.MyUdt";

    // Execute the stored procedure
    cmd.ExecuteNonQuery();
}

So, this is the solution.

Finally I want to prevent you from using code like yours (building the strings and then execute this string), because this way of executing may be used for SQL-Injections.

Comments

[David.Chu.ca]

How about calling SP instead of INERT sql? The SP will take the similar parameters, but within the SP there is commit for each call. Will the transaction be rolled back if any of SP calls fails?

[uSeRnAmEhAhAhAhAhA]

@Kevin, why doesn't CommitTransaction not show up in Intellisense?

[kemiller2002]

In truth, I don't know. I'll have to track down why @Spike

[uSeRnAmEhAhAhAhAhA]

@Kevin no worries :) It was just a thought.