大众计算机学习网

专门提供各种电脑教程

sqlserver2005快速插入或导入百万条记录

Admin | 2014-8-30 8:12:32 | 被阅次数 | 3154

  第一种方法:基本就是使用命令导入TXT中的数据到SQL中。

  注意:文件字段使用逗号分隔,行使用“|”分隔。代码如下:

  SqlProvider.ExecuteNonQuery("BULK INSERT Customer FROM 'c:\\MyData.txt' WITH (FIELDTERMINATOR = ',',ROWTERMINATOR='|',BATCHSIZE = 100000)");

  第二种方法:使用代码来实现

  代码功能:构建100000数据:

  private void GetData()
        {
            try
            {
                dt = new DataTable();
                dt.Columns.Add("id", typeof(int));
                dt.Columns.Add("name", typeof(string));
                dt.Columns.Add("email", typeof(string));
                dt.Columns.Add("address", typeof(string));
                dt.Columns.Add("phone", typeof(string));
                for (int i = 0; i < 1000000; i++)
                {
                    DataRow dr = dt.NewRow();
                    dr["id"] = i;
                    dr["name"] = "name_" + i;
                    dr["email"] = "email_" + i;
                    dr["address"] = "address_" + i;
                    dr["phone"] = "phone_" + i;
                    dt.Rows.Add(dr);
                }
            }
            finally
            {
            }
        }

  protected void F1()
        {
            SqlConnection conn = new SqlConnection(str);
            conn.Open();
            System.Diagnostics.Stopwatch timer = new Stopwatch();
            using (System.Data.SqlClient.SqlBulkCopy sqlBC = new System.Data.SqlClient.SqlBulkCopy(conn))
            {
                sqlBC.BatchSize = 100000;
                sqlBC.BulkCopyTimeout = 60;
                sqlBC.Destinati;
                sqlBC.ColumnMappings.Add("id", "customerId");
                sqlBC.ColumnMappings.Add("name", "name");
                sqlBC.ColumnMappings.Add("email", "email");
                sqlBC.ColumnMappings.Add("address", "address");
                sqlBC.ColumnMappings.Add("phone", "phone");
                timer.Start();
                sqlBC.WriteToServer(dt);
                timer.Stop();
            }
            conn.Dispose();
            MessageBox.Show(timer.ElapsedMilliseconds.ToString());
           
        }

  使用第二种办法的话,一般一分钟不到,就能插入十万条记录了。