SQL SERVER MERGE语句用法

语法：

-- SQL Server和Azure SQL数据库
--‘::=’意义为相等于
[ WITH <common_table_expression> [,...n] ]  
MERGE
    [ TOP ( expression ) [ PERCENT ] ]
    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]  
    USING <table_source> [ [ AS ] table_alias ]
    ON <merge_search_condition>  
    [ WHEN MATCHED [ AND <clause_search_condition> ]  
        THEN <merge_matched> ] [ ...n ]  
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]  
        THEN <merge_not_matched> ]  
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]  
        THEN <merge_matched> ] [ ...n ]  
    [ <output_clause> ]  
    [ OPTION ( <query_hint> [ ,...n ] ) ]
;  

<target_table> ::=  
{
--即<target_table>处的内容应填充大括号内语法格式的内容
    [ database_name . schema_name . | schema_name . ]  
  target_table  
}  

<merge_hint>::=  
{  
    { [ <table_hint_limited> [ ,...n ] ]  
    [ [ , ] INDEX ( index_val [ ,...n ] ) ] }  
}  

<merge_search_condition> ::=  
    <search_condition>  

<merge_matched>::=  
    { UPDATE SET <set_clause> | DELETE }  

<merge_not_matched>::=  
{  
    INSERT [ ( column_list ) ]
        { VALUES ( values_list )  
        | DEFAULT VALUES }  
}  

<clause_search_condition> ::=  
    <search_condition> 

使用：

BEGIN
    MERGE
--表明dbo.DataInput_RDPC表为目标表
        dbo.DataInput_RDPC AS Target1
    USING 
    --以姓名电话更新
--Inserted 表为插入临时表，从插入临时表中与目标表匹配
        Inserted Ins ON Ins.Name = Target1.Name AND Ins.Phone = Target1.Phone
    WHEN MATCHED THEN 
--如果姓名且电话匹配，更新目标表中内容，以插入临时表中匹配到的内容更新
--直接设置更新字段
        UPDATE SET 
        Target1.cityId = Ins.cityId,Target1.areaId = Ins.areaId,Target1.townId = Ins.townId,Target1.villageId = Ins.villageId,
        Target1.Name = Ins.Name,Target1.Gender = Ins.Gender,
        Target1.Nation = Ins.Nation,Target1.NativePlace = Ins.NativePlace,Target1.Birthday = Ins.Birthday,Target1.Parties = Ins.Parties,
        Target1.Education = Ins.Education,Target1.WorkDate = Ins.WorkDate,
        Target1.WorkAddress = Ins.WorkAddress,Target1.Constituency = Ins.Constituency,Target1.Phone = Ins.Phone,
        Target1.Remark = Ins.Remark
    WHEN NOT MATCHED THEN
--如果姓名或电话不匹配，向目标表中插入新的内容
--直接设置插入的字段，此处未设置表名可能默认为目标表
        INSERT(           cityId,areaId,townId,villageId,Name,Gender,Nation,NativePlace,Birthday,Parties,Education,WorkAddress,Constituency,Phone,WorkDate,Remark) 
        VALUES( 
--直接设置插入的字段值，此处未用表名区分可能是从插入临时表中获取单条数据插入    
      cityId,areaId,townId,villageId,Name,Gender,Nation,NativePlace,Birthday,Parties,Education,WorkAddress,Constituency,Phone,WorkDate,Remark); 
   
    -- 最后删除临时表的数据
    DELETE Temp_DataInput_RDPC
END

官网示例：

MERGE (Transact-SQL) - SQL Server | Microsoft Docs

附.net mvc批量拷贝数据方法：

需在泛型类中创建一下方法

///泛型类
public class DALClass<T> where T : new(){
/// <summary>
/// list转换为DtaSet
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <returns></returns>
public DataSet ConvertToDataSet<T>(IList<T> list)
{
    if (list == null || list.Count <= 0)
    {
        return null;
    }
    DataSet ds = new DataSet();
    DataTable dt = ConvertToDataTable(list);
    ds.Tables.Add(dt);
    return ds;
}
/// <summary>
/// list转换为DataTable
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <returns></returns>
public DataTable ConvertToDataTable<T>(IList<T> list)
{
    if (list == null || list.Count <= 0)
    {
        return null;
    }
    DataTable dt = new DataTable(typeof(T).Name);
    DataColumn column;
    DataRow row;
    System.Reflection.PropertyInfo[] myPropertyInfo = typeof(T).GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);
    foreach (T t in list)
    {
        if (t == null)
        {
            continue;
        }
        row = dt.NewRow();
        for (int i = 0, j = myPropertyInfo.Length; i < j; i++)
        {
            System.Reflection.PropertyInfo pi = myPropertyInfo[i];
            string name = pi.Name;
            if (dt.Columns[name] == null)
            {
                column = new DataColumn(name, pi.PropertyType);
                dt.Columns.Add(column);
            }
            row[name] = pi.GetValue(t, null);
        }
        dt.Rows.Add(row);
    }
    return dt;
}
/// <summary>
/// 获取当前实体的类成员属性
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
public Dictionary<object, object> GetProperties(T t)
{
    var ret = new Dictionary<object, object>();
    if (t == null) { return null; }
    //指定成员搜索条件
    PropertyInfo[] properties = t.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);
    if (properties.Length <= 0) { return null; }
    foreach (PropertyInfo item in properties)
    {
        string name = item.Name;
        object type = item.PropertyType.Name;
        if (item.PropertyType.IsValueType || item.PropertyType.Name.StartsWith("String"))
        {
            ret.Add(name, type);
        }
    }
    return ret;
}
/// <summary>
/// 建立映射关系
/// </summary>
/// <param name="dir"></param>
/// <returns></returns>
public SqlBulkCopyColumnMapping[] GetMapping(Dictionary<object, object> dir)
{
    string[] sKeys = new string[dir.Count];
    dir.Keys.CopyTo(sKeys, 0);
    SqlBulkCopyColumnMapping[] mapping = new SqlBulkCopyColumnMapping[dir.Count];
    for (int i = 0; i < sKeys.Length; i++)
    {
        mapping[i] = new SqlBulkCopyColumnMapping(sKeys[i], sKeys[i]);
    }
    return mapping;
}
/// <summary>
/// DataTable批量添加(有事务)
/// </summary>
/// <param name="Table">数据源DataTable</param>
/// <param name="DestinationTableName">目标表即需要插入数据的数据表名称</param>
public bool MySqlBulkCopy(DataTable Table, string DestinationTableName, Dictionary<object, object> dir)
{
    bool Bool = true;
    //数据库链接字符串
    string ConnectionStringCustomers = ConfigurationManager.ConnectionStrings["connect"].ConnectionString;
    using (SqlConnection con = new SqlConnection(ConnectionStringCustomers))
    {
        con.Open();
        using (SqlTransaction Tran = con.BeginTransaction())//应用事物
        {
            using (SqlBulkCopy Copy = new SqlBulkCopy(con, SqlBulkCopyOptions.FireTriggers, Tran))//触发器
            {
                Copy.DestinationTableName = DestinationTableName;//指定目标表
                SqlBulkCopyColumnMapping[] Mapping = GetMapping(dir);//获取映射关系
                if (Mapping != null)
                {
                    //如果有数据
                    foreach (SqlBulkCopyColumnMapping Map in Mapping)
                    {
                        Copy.ColumnMappings.Add(Map);
                    }
                }
                try
                {
                    Copy.WriteToServer(Table);//批量添加
                    Tran.Commit();//提交事务
                }
                catch (Exception ex)
                {
                    Tran.Rollback();//回滚事务
                    Bool = false;
                }
            }
        }
    }
    return Bool;
}
}

调用方法：

 

YouClass mod = new YouClass();
//取得要导入对象中所有字段 除了主键
var obj = new DALClass<YouClass>().GetPropertiesIncludeId(mod);
//tmp_YouClass为实体类对应的数据库表
if (new DALClass<YouClass>().MySqlBulkCopy(new DALClass<YouClass>().ConvertToDataTable(updateList), "tmp_YouClass", obj))
{
    LogHelper.WriteWithTime("事务执行成功");
}
else
{
    LogHelper.WriteWithTime("事务执行失败");
}