public static void StandardMergeSP(DatabaseObject table, DirectoryInfo outputFolder, string LoadingSchema, string prefix)
{
string sqlScript = Templates.GetTemplateContent(TemplateFolder, "Load_StandardMerge.sql");
IEnumerable <DatabaseColumn> joinColumns = table.Columns.Where(column => column.IsPrimaryKey == true);
if (prefix == null)
{
prefix = string.Empty;
}
if (joinColumns.Count() == 0)
{
Console.WriteLine();
Console.WriteLine("Target table has no primary key(s) so cannot create correctly formed MERGE statement {0}", table.DatabaseObjectName);
Console.WriteLine();
}
IEnumerable <DatabaseColumn> updateColumns;
if (joinColumns.Count() >= 1)
{
updateColumns = table.Columns.Where(column => column.IsPrimaryKey == false && column.IsIdentity == false && !DatabaseObject.nonUpdateableStandardColumns.Contains(column.DatabaseColumnName.ToLower()) && column.DatabaseColumnName != joinColumns.First().DatabaseColumnName);
}
else
{
updateColumns = table.Columns.Where(column => column.IsPrimaryKey == false && column.IsIdentity == false && !DatabaseObject.nonUpdateableStandardColumns.Contains(column.DatabaseColumnName.ToLower()));
}
IEnumerable <DatabaseColumn> whenMatchedColumns;
if (joinColumns.Count() >= 1)
{
whenMatchedColumns = table.Columns.Where(column => column.IsPrimaryKey == false && column.IsIdentity == false && !DatabaseObject.allStandardColumns.Contains(column.DatabaseColumnName.ToLower()) && column.DatabaseColumnName != joinColumns.First().DatabaseColumnName);
}
else
{
whenMatchedColumns = table.Columns.Where(column => column.IsPrimaryKey == false && column.IsIdentity == false && !DatabaseObject.allStandardColumns.Contains(column.DatabaseColumnName.ToLower()));
}
IEnumerable <DatabaseColumn> insertColumns = table.Columns.Where(column => column.DatabaseColumnName.ToLower() != DatabaseObject.updatedloadlogid && column.DatabaseColumnName.ToLower() != DatabaseObject.loadlogid && column.IsIdentity == false);
IEnumerable <DatabaseColumn> identityColumns = table.Columns.Where(column => column.IsIdentity);
string andClauseForDelete = string.Empty;
if (table.DatabaseObjectName.ToLower().StartsWith("dim") && identityColumns.Count() >= 1)
{
andClauseForDelete = string.Format("and ({0} > -1) ", identityColumns.First().DatabaseColumnName);
}
string sql = string.Format(sqlScript,
LoadingSchema, // {0} = loading schema name
table.DatabaseObjectName, // {1} = table name
table.SchemaName, // {2} = table schema
table.GetJoinColumnSql(joinColumns, "T", "S", false, false), // {3} = join criteia
table.GetJoinColumnSql(whenMatchedColumns, "T", "S", false, true), // {4} = when matched critera
table.GetUpdateColumnSql(updateColumns, "T", "S"), // {5} = update column list
table.GetColumnListSql(insertColumns, string.Empty), // {6} = insert list
table.GetColumnListSql(insertColumns, "S"), // {7} = insert value list
prefix, // {8} = prefix for the stored proc name
andClauseForDelete // {9} = and clause for the delete part of merge
);
string sqlPath = Path.Combine(outputFolder.FullName, string.Format("Load_{0}{1}.sql", prefix, table.DatabaseObjectName));
Console.Write(".");
System.IO.File.WriteAllText(sqlPath, sql);
}