Bulk Inserts into SQL

Following on from the article on downloading prices from Google, today we shall focus on how to quickly and efficiently insert them into an SQL database. On an average day I will download some 750k prices - if I were to insert them record-by-record using a Linq statement, it would take in excess of half and hour. To get round these problems, use the following code to perform a bulk insert into the database:

 public static void CopyData(DataTable sourceTable, string dbTable)
        {
            using (var cn = new SqlConnection(Database.ConnectionString))
            {
                cn.Open();

                using (var s = new SqlBulkCopy(Database.ConnectionString))
                {
                    s.DestinationTableName = dbTable;
                    s.NotifyAfter = 10000;
                    s.SqlRowsCopied += SqlRowsCopied;
                    s.WriteToServer(sourceTable);
                    s.Close();
                }
            }
        }

        private static void SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
        {
            Console.WriteLine("-- Copied {0} rows.", e.RowsCopied);
        }

The datatable 'sourceTable' is formatted to exactly match the SQL table (name, data type etc).  The method SqlRowsCopied just keeps you updated as to where it is in the insert process.

With this method you can easily insert all 750k records within a matter of seconds.