Insert 2 million rows into SQL Server quickly

Question

I want to insert about 2 million rows from a text file.

And with inserting, I want to create some master tables.

What is the most reliable and fast way to insert such a large set of data into the SQL Server?

Answer 1

You can try with this method and it will significantly reduce the database insert execution time.

List<string> toinsert = new List<string>();
StringBuilder insertCmd = new StringBuilder("INSERT INTO tabblename (col1, col2, col3) VALUES ");

foreach (var row in rows)
{
      // the point here is to keep values quoted and avoid SQL injection
      var first = row.First.Replace("'", "''")
      var second = row.Second.Replace("'", "''")
      var third = row.Third.Replace("'", "''")

      toinsert.Add(string.Format("( '{0}', '{1}', '{2}' )", first, second, third));
}
if (toinsert.Count != 0)
{
      insertCmd.Append(string.Join(",", toinsert));
      insertCmd.Append(";");
}
using (MySqlCommand myCmd = new MySqlCommand(insertCmd.ToString(), SQLconnectionObject))
{
      myCmd.CommandType = CommandType.Text;
      myCmd.ExecuteNonQuery();
}

*Create the SQL connection object and replace it where I have written the SQLconnectionObject.

Are you interested to learn SQL in detail? Sign up for this perfect SQL Training by Intellipaat.