Back

Explore Courses Blog Tutorials Interview Questions
0 votes
1 view
in SQL by (6.1k points)
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?

1 Answer

0 votes
by (12.7k points)

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.

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

Browse Categories

...