protected virtual string InsertFromStagingSql()
{
string whenNotMatchedStatement = $@"WHEN NOT MATCHED BY TARGET THEN
INSERT ({string.Join(", ", PropertiesToInsert.Select(i => $"[{i.GetColumnName(base.StoreObject)}]"))})
VALUES ({string.Join(", ", PropertiesToInsert.Select(i => $"S.[{i.GetColumnName(base.StoreObject)}]"))})";
return($@"MERGE {SqlTargetTable} AS T
USING {SqlStagingTableName} AS S
ON 1=0
{whenNotMatchedStatement}
OUTPUT INSERTED.*, S.[{TempColumnNumOrderName}], $action
INTO {SqlOutputStagingTableName} option(recompile);");
}
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};");
}
protected virtual string MergeFromStagingSql(bool doNotUpdateIfExists = false)
{
var pivotColumns = PropertiesForPivotSet.SelectMany(p => p.Select(i => i.GetColumnName(base.StoreObject))).ToHashSet();
string whenNotMatchedStatement = $@"WHEN NOT MATCHED BY TARGET THEN
INSERT ({string.Join(", ", PropertiesToInsert.Select(i => $"[{i.GetColumnName(base.StoreObject)}]"))})
VALUES ({string.Join(", ", PropertiesToInsert.Select(i => $"S.[{i.GetColumnName(base.StoreObject)}]"))})";
string whenMatchedStatement = this.GetWhenMatchedMergeStatement(pivotColumns, doNotUpdateIfExists);
string pivotCriteria = this.BuildPivotCriteria();
return($@"MERGE {SqlTargetTable} AS T
USING {SqlStagingTableName} AS S
ON {pivotCriteria}
{whenNotMatchedStatement}
{whenMatchedStatement}
OUTPUT INSERTED.*, S.[{TempColumnNumOrderName}], $action
INTO {SqlOutputStagingTableName} option(recompile);");
}