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}"
};
}