Sql server2008中merge用法

 　　　　 /// <summary>
        /// 修改:添加条件: AND roleModule.FuncCode = tvpRoleModule.FuncCode
        /// </summary>
        private static readonly string SQL_PernissionReRoleModuleCreate = @"MERGE [dbo].[PernissionReRoleModule] AS roleModule
USING @tvp AS tvpRoleModule
ON (roleModule.RoleId = tvpRoleModule.RoleId AND roleModule.ModuleId = tvpRoleModule.ModuleId AND roleModule.FuncCode = tvpRoleModule.FuncCode)
WHEN MATCHED
 THEN UPDATE SET roleModule.FuncCode = tvpRoleModule.FuncCode,roleModule.OptScope = tvpRoleModule.OptScope
WHEN NOT MATCHED THEN
  INSERT(RoleId,ModuleId,FuncCode,OptScope)
        VALUES(tvpRoleModule.RoleId,tvpRoleModule.ModuleId,tvpRoleModule.FuncCode,tvpRoleModule.OptScope)
WHEN NOT MATCHED BY SOURCE AND roleModule.RoleId  = @roleId
     THEN DELETE;";


        /// <summary>
        /// 创建新的角色功能信息
        /// </summary>
        /// <param name="organization">角色功能实体类</param>
        /// <returns></returns>
        public int Create(IList<PernissionReRoleModule> module, int roleId)
        {

            DataTable tblData = new DataTable();
            tblData.Columns.Add("RoleId", typeof(Int32));
            tblData.Columns.Add("ModuleId", typeof(Int32));
            tblData.Columns.Add("FunCode", typeof(int));
            tblData.Columns.Add("OptScope", typeof(int));
            foreach (var item in module)
            {
                tblData.Rows.Add(new object[] { roleId, item.ModuleId, item.FuncCode, item.OptScope });
            }

            var cmd = this.sqlHelper.PrepareTextSqlCommand(SQL_PernissionReRoleModuleCreate);
            cmd.AddParam("@roleId", roleId);
            var param = cmd.Parameters.AddWithValue("@tvp", tblData);


            param.SqlDbType = SqlDbType.Structured;
            param.TypeName = "dbo.ReRoleModule";
            return cmd.ExecuteWrapNonQuery();
        }
        #endregion