private void BuildScriptWithMaskSQL(Table table, IDataWrapper data, IDataFilter filter, out object script)
{
string destTable = ProcessTableName(table.DestName, table.DestSchema);
string tmpTable = ProcessTableName($"{ExtractTableName(table.DestName)}_{Guid.NewGuid():N}", table.DestSchema);
BuildScriptWithDataTable(table, data, filter, out script);
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 {tmpTable} 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}");
}
script = new MergeScript()
{
TableName = tmpTable,
PrepareSQL = $"SELECT {ProcessFieldNames(table.DestFields)} INTO {tmpTable} FROM {destTable} WHERE 1 = 0",
Data = ((AppendScript)script).Data,
MergeSQL = sb.ToString(),
MergeSQL2 = "",
CleanSQL = $"DROP TABLE {tmpTable}"
};
}
private void BuildScriptWithMergeSQL(Table table, IDataWrapper data, IDataFilter filter, out object script)
{
string destTable = ProcessTableName(table.DestName, table.DestSchema);
string tmpTable = ProcessTableName($"{ExtractTableName(table.DestName)}_{Guid.NewGuid():N}", table.DestSchema);
string mergeSQL, mergeSQL2;
BuildScriptWithDataTable(table, data, filter, out script);
if (Version.Parse(conn.ServerVersion).Major >= 10) // 2008 或更新版本
{
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 {tmpTable} 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")});"); // 语句以分号结尾
mergeSQL = sb.ToString();
mergeSQL2 = "";
}
else
{
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 {tmpTable} 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}");
}
mergeSQL = sb.ToString();
fields = ExcludeFields(table.DestFields, table.SkipFields);
sb.Length = 0;
field = ProcessFieldName(table.KeyFields[0]);
sb.Append($"INSERT INTO {destTable} ({ProcessFieldNames(fields)}) SELECT")
.Append($" {ProcessFieldNames(fields, "A")} FROM {tmpTable} A LEFT JOIN {destTable} B ON")
.Append($"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}");
}
sb.Append($" WHERE B.{field} IS NULL");
mergeSQL2 = sb.ToString();
}
script = new MergeScript()
{
TableName = tmpTable,
PrepareSQL = $"SELECT * INTO {tmpTable} FROM {destTable} WHERE 1 = 0",
Data = ((AppendScript)script).Data,
MergeSQL = mergeSQL,
MergeSQL2 = mergeSQL2,
CleanSQL = $"DROP TABLE {tmpTable}"
};
}
