Sqlserver高效率数据导入

 string strConn = "Data Source=192.18.11.200;Initial Catalog=dbXianNew;user=sa;pwd=test;";
            SqlConnection conn = new SqlConnection(strConn);

            //源连接
            conn.Open();

            //查询元数据
            string strSql = "select * from XiAnData where (LU_MING is not null or LU_MING<>'') and (FANG_JIAN_HAO is not null or FANG_JIAN_HAO<>'') " +
                "and not ((JIAN_ZHU_MJ1 is null or JIAN_ZHU_MJ1='') and (JIAN_ZHU_MJ2 is null or JIAN_ZHU_MJ2='') and (SHI_YONG_MJ is null or SHI_YONG_MJ=''))";


            SqlCommand cmd = new SqlCommand(strSql, conn);

            SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);


            DataTable table = new DataTable();
            table.Columns.Add("id", typeof(System.Guid));
            table.Columns.Add("di_zhi", typeof(String));
            table.Columns.Add("dan_yuan", typeof(String));
            table.Columns.Add("fang_jian_hao", typeof(String));
            table.Columns.Add("hu_xing", typeof(String));
            table.Columns.Add("mian_ji", typeof(String));


            while (dr.Read())
            {
                DataRow row = table.NewRow();

                row["id"] = dr["ID"].ToString();

                Regex r; // 声明一个 Regex类的变量
                r = new Regex("\\d"); // 定义表达式

                if (dr["HAO"] != null && dr["HAO"].ToString() != "" && dr["ZUO"] != null && dr["ZUO"].ToString() != "")
                {
                    row["di_zhi"] = dr["LU_MING"].ToString() + dr["HAO"].ToString() + "号" + dr["ZUO"].ToString() + "座";
                }
                else if (dr["HAO"] != null && dr["HAO"].ToString() != "")
                {
                    row["di_zhi"] = dr["LU_MING"].ToString() + dr["HAO"].ToString() + "号";
                }
                else if (r.Match(dr["LU_MING"].ToString()).Success)
                {
                    row["di_zhi"] = dr["LU_MING"].ToString();
                }

                if (dr["DAN_YUAN1"] != null && dr["DAN_YUAN1"].ToString() != "")
                {
                    row["dan_yuan"] = dr["DAN_YUAN1"].ToString();
                }
                else if (dr["FANG_JIAN_HAO"] != null && dr["FANG_JIAN_HAO"].ToString() != "" && dr["FANG_JIAN_HAO"].ToString().Length > 4)
                {
                    row["dan_yuan"] = dr["FANG_JIAN_HAO"].ToString().Substring(0, dr["FANG_JIAN_HAO"].ToString().Length - 4);
                }

                row["fang_jian_hao"] = dr["FANG_JIAN_HAO"].ToString();
                row["hu_xing"] = dr["HU_XING"].ToString();

                if (dr["SHI_YONG_MJ"] != null && dr["SHI_YONG_MJ"].ToString() != "" && dr["SHI_YONG_MJ"].ToString() != "0")
                {
                    row["mian_ji"] = dr["SHI_YONG_MJ"].ToString();
                }
                else if (dr["JIAN_ZHU_MJ1"] != null && dr["JIAN_ZHU_MJ1"].ToString() != "" && dr["JIAN_ZHU_MJ1"].ToString() != "0")
                {
                    row["mian_ji"] = dr["JIAN_ZHU_MJ1"].ToString();
                }
                else if (dr["JIAN_ZHU_MJ2"] != null && dr["JIAN_ZHU_MJ2"].ToString() != "" && dr["JIAN_ZHU_MJ2"].ToString() != "0")
                {
                    row["mian_ji"] = dr["JIAN_ZHU_MJ2"].ToString();
                }

                if (!string.IsNullOrEmpty(row["di_zhi"].ToString()) && !string.IsNullOrEmpty(row["fang_jian_hao"].ToString()) && !string.IsNullOrEmpty(row["mian_ji"].ToString()))
                {
                    table.Rows.Add(row);
                }
            }

            dr.Close();

            conn.Open();

            using (SqlBulkCopy bc = new SqlBulkCopy(conn))
            {
                bc.BatchSize = 50000;
                bc.BulkCopyTimeout = 50000;
                bc.DestinationTableName = "t_xian1";
                bc.WriteToServer(table);
            }

            conn.Close();

 

转载于:https://www.cnblogs.com/Kakasi/archive/2013/03/15/2961401.html