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};");
        }
예제 #2
0
 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}]";
     }))}");
 }