private void BuildScriptWithMaskSQL(Table table, IDataWrapper data, IDataFilter filter, out object script) { string destTable = ProcessTableName(table.DestName, table.DestSchema); string tmpTable = $"{ExtractTableName(table.DestName)}_{Guid.NewGuid():N}"; string processedTmpTable = ProcessTableName(tmpTable, table.DestSchema); StringBuilder sb = new StringBuilder(); string[] fields = ExcludeFields(table.DestFields, table.KeyFields, table.SkipFields); string field = ProcessFieldName(fields[0]); sb.Append($"UPDATE {destTable} SET {field} = B.{field}"); for (int i = 1; i < fields.Length; i++) { field = ProcessFieldName(fields[i]); sb.Append($", {field} = B.{field}"); } field = ProcessFieldName(table.KeyFields[0]); sb.Append($" FROM {processedTmpTable} B WHERE {destTable}.{field} = B.{field}"); for (int i = 1; i < table.KeyFields.Length; i++) { field = ProcessFieldName(table.KeyFields[i]); sb.Append($" AND {destTable}.{field} = B.{field}"); } BuildScriptWithInsertSQL(table, tmpTable, data, filter, out script); script = new MergeScript() { TableName = tmpTable, PrepareSQL = $"CREATE TABLE {processedTmpTable} AS" + $" (SELECT {ProcessFieldNames(table.DestFields)} FROM {destTable} WHERE 1 = 0) WITH NO DATA", InsertSQL = script.ToString(), MergeSQL = sb.ToString(), CleanSQL = $"DROP TABLE {processedTmpTable}" }; }
private void BuildScriptWithMergeSQL(Table table, IDataWrapper data, IDataFilter filter, out object script) { string destTable = ProcessTableName(table.DestName, table.DestSchema); string tmpTable = $"{ExtractTableName(table.DestName)}_{Guid.NewGuid():N}"; string processedTmpTable = ProcessTableName(tmpTable, table.DestSchema); StringBuilder sb = new StringBuilder(); string[] fields = ExcludeFields(table.DestFields, table.KeyFields, table.SkipFields); string field = ProcessFieldName(table.KeyFields[0]); sb.Append($"MERGE INTO {destTable} A USING {processedTmpTable} B ON A.{field} = B.{field}"); for (int i = 1; i < table.KeyFields.Length; i++) { field = ProcessFieldName(table.KeyFields[i]); sb.Append($" AND A.{field} = B.{field}"); } field = ProcessFieldName(fields[0]); sb.AppendLine().Append($" WHEN MATCHED THEN UPDATE SET A.{field} = B.{field}"); for (int i = 1; i < fields.Length; i++) { field = ProcessFieldName(fields[i]); sb.Append($", A.{field} = B.{field}"); } fields = ExcludeFields(table.DestFields, table.SkipFields); sb.AppendLine().Append($" WHEN NOT MATCHED THEN INSERT ({ProcessFieldNames(fields)}") .Append($") VALUES ({ProcessFieldNames(fields, "B")});"); // 语句以分号结尾 BuildScriptWithInsertSQL(table, tmpTable, data, filter, out script); script = new MergeScript() { TableName = tmpTable, PrepareSQL = $"CREATE TABLE {processedTmpTable} LIKE {destTable}", InsertSQL = script.ToString(), MergeSQL = sb.ToString(), CleanSQL = $"DROP TABLE {processedTmpTable}" }; }