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?

Answer 1

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.