C# Sql Server海量数据批量入库及更新

保证参数DataTable dt 与数据库内原表结构一样 并dt.TableName值为原表名

原理:

创建本次连接内有效的临时表,将批量数据写入临时表,然后根据主键字段将原表没有的数据插入目的表,有则更新

优点:

不更改目的数据库结构

海量数据秒级入库

代码如下:

public int InsertSqlBulk(DataTable dt,string SqlConnString)
        {
            int insertnum = 0;
            int updatenum = 0;
            try
            {
                using (SqlConnection conn = new SqlConnection(SqlConnString))
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    StringBuilder sb = new StringBuilder();
                    DataTable pk = new DataTable();
                    string tempTableName = "#bulktemp";//#表名 为当前连接有效的临时表 ##表名 为全局有效的临时表
                    string filedstr = "";
                    string filedsetstr = "";
                    string pkfiledwherestr = "";
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        if (i > 0)
                        {
                            filedstr += ",";
                            filedsetstr += ",";
                        }
                        filedstr += "s." + dt.Columns[i].ColumnName;
                        filedsetstr += "" + dt.Columns[i].ColumnName + "=t." + dt.Columns[i].ColumnName;
                    }
                    sb = new StringBuilder();
                    sb.AppendFormat("select name from syscolumns where colid in (select colid from sysindexkeys where id = object_id('{0}') and indid = (select indid from sysindexes where name = (select name from sysobjects where xtype='pk' and parent_obj = object_id('{0}')))) and syscolumns.id = object_id('{0}');", dt.TableName);
                    cmd.CommandText = sb.ToString();
                    pk.Load(cmd.ExecuteReader());//查询主键列表
                    for (int i = 0; i < pk.Rows.Count; i++)
                    {
                        if (i > 0)
                        {
                            pkfiledwherestr += " and ";
                        }
                        pkfiledwherestr += "t." + pk.Rows[i]["name"] + "=s." + pk.Rows[i]["name"];
                    }
                    sb = new StringBuilder();
                    sb.AppendFormat("select top 0 {0} into {1} from {2} s;", filedstr, tempTableName, dt.TableName);
                    cmd.CommandText = sb.ToString();
                    cmd.ExecuteNonQuery();//创建临时表
                    using (System.Data.SqlClient.SqlBulkCopy bulk = new System.Data.SqlClient.SqlBulkCopy(conn))
                    {
                        bulk.DestinationTableName = tempTableName;
                        bulk.BulkCopyTimeout = 36000;
                        try
                        {
                            bulk.WriteToServer(dt);//将数据写入临时表
                        }
                        catch(Exception e) {
                            
                        }
                    }

                    if (pkfiledwherestr.Equals(""))//如果不存在主键
                    {
                        sb = new StringBuilder();
                        sb.AppendFormat("insert into {0} select {1} from {2} s;", dt.TableName, filedstr, tempTableName);
                        cmd.CommandText = sb.ToString();
                        insertnum = cmd.ExecuteNonQuery();//插入临时表数据到目的表
                    }
                    else
                    {
                        sb = new StringBuilder();
                        sb.AppendFormat("update {0} set {1} from {0} s INNER JOIN {2} t on {3}", dt.TableName, filedsetstr, tempTableName, pkfiledwherestr);
                        cmd.CommandText = sb.ToString();
                        updatenum = cmd.ExecuteNonQuery();//更新已存在主键数据

                        sb = new StringBuilder();
                        sb.AppendFormat("insert into {0} select {1} from {2} s where not EXISTS(select 1 from {0} t where {3});", dt.TableName, filedstr, tempTableName, pkfiledwherestr);
                        cmd.CommandText = sb.ToString();
                        insertnum = cmd.ExecuteNonQuery();//插入新数据

                    }
                }
            }
            catch(Exception e)
            {
                
            }

            return insertnum;//返回插入条数
        }