protected virtual string MergeFromStagingSql()
{
string whenNotMatchedStatement = $@"WHEN NOT MATCHED BY TARGET THEN
INSERT ({string.Join(", ", PropertiesToInsert.Select(i => i.SqlServer().ColumnName))})
VALUES ({string.Join(", ", PropertiesToInsert.Select(i => $"S.{i.SqlServer().ColumnName}"))})";
string whenMatchedStatement = PropertiesToUpdate.Count == 0 ? "" : $@"WHEN MATCHED THEN
UPDATE SET {string.Join(", ", PropertiesToUpdate.Select(i => $"T.{i.SqlServer().ColumnName} = S.{i.SqlServer().ColumnName}"))}";
return($@"MERGE {SqlTargetTable} WITH (HOLDLOCK) AS T
USING {SqlStagingTableName} AS S
ON {string.Join(" AND ", PropertiesForPivot.Select(i => CreateEqualityConditionSql("T", "S", i)))}
{whenNotMatchedStatement}
{whenMatchedStatement}
OUTPUT INSERTED.*, S.{TempColumnNumOrderName}
INTO {SqlOutputStagingTableName};");
}
private string GetWhenMatchedMergeStatement(HashSet <string> pivotColumns, bool doNotUpdateIfExists)
{
if (PropertiesToUpdate.Count == 0)
{
return("");
}
if (doNotUpdateIfExists)
{
return($@"WHEN MATCHED THEN
UPDATE SET {string.Join(", ", PropertiesToUpdate.Select(i => i.GetColumnName(base.StoreObject)).Select(columnName => $"T.[{columnName}] = T.[{columnName}]").ToArray())}");
}
return($@"WHEN MATCHED THEN
UPDATE SET {string.Join(", ", PropertiesToUpdate.Select(i =>
{
var columnName = i.GetColumnName(base.StoreObject);
if (pivotColumns.Contains(columnName) && i.IsColumnNullable())
return $"T.[{columnName}] = ISNULL(T.[{columnName}], S.[{columnName}])";
else
return $"T.[{columnName}] = S.[{columnName}]";
}))}");
}