title: SQL Insert one row or multiple rows data?
body: <p>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:</p>
  
  <pre><code>List&lt;Employee&gt; employees;
  </code></pre>
  
  <p>What I can do is to insert one object at time like this:</p>
  
  <pre><code>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();
  }
  </code></pre>
  
  <p>Or I can build a balk insert query like this:</p>
  
  <pre><code>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();
  </code></pre>
  
  <p>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?</p>
  
  <p>I am not sure if one insert with multiple rows is better than one insert with one row of data, in terms of performance?</p>
  
  <p>Any suggestions to do it in a better way?</p>