public static void SimpleMergeSP(DatabaseObject table, DirectoryInfo outputFolder, string LoadingSchema, string prefix) { string sqlScript = Templates.GetTemplateContent(TemplateFolder, "Load_SimpleMerge.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> 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.GetUpdateColumnSql(updateColumns, "T", "S"), // {4} = update column list table.GetColumnListSql(insertColumns, string.Empty), // {5} = insert list table.GetColumnListSql(insertColumns, "S"), // {6} = insert value list prefix, // {7} = prefix for the stored proc name andClauseForDelete // {8} = 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); }
private static string InsertUnknowns(string LoadingSchema, DatabaseObject table) { string sqlScript = Templates.GetTemplateContent(DatabaseName, "InsertUnknowns.sql"); string sql = string.Empty; if (table.Columns.Count(column => column.IsPrimaryKey) > 0) { string setIdentity = "--"; if (table.Columns.Where(column => column.IsIdentity == true).Count() > 0) { setIdentity = string.Empty; } string[] excludeColumnsFromInsertView = { DatabaseObject.updatedloadlogid }; IEnumerable <DatabaseColumn> filteredColumns = table.Columns.Where(column => (!excludeColumnsFromInsertView.Contains(column.DatabaseColumnName.ToLower()) && !column.IsIdentity && !column.IsNullable)); sql = string.Format(sqlScript, table.SchemaName, table.DatabaseObjectName, table.DatabaseObjectName + "Key", table.GetColumnListSql(filteredColumns, string.Empty), table.GetUnknownList(filteredColumns, "N'UNKNOWN'", "N'U'", -1), -1, setIdentity); } return(sql); }
public static void StandardInsertSP(DatabaseObject table, DirectoryInfo outputFolder, string LoadingSchema, string prefix) { string sqlScript = Templates.GetTemplateContent(TemplateFolder, "Load_StandardInsert.sql"); if (prefix == null) { prefix = string.Empty; } string sql = string.Format(sqlScript, LoadingSchema, // {0} = loading schema name table.DatabaseObjectName, // {1} = table name table.SchemaName, // {2} = table schema table.GetColumnListSql(table.Columns.Where(column => !DatabaseObject.standardColumns.Contains(column.DatabaseColumnName.ToLower())), string.Empty), // {3} = column list prefix // {4} = prefix for the stored proc name ); string sqlPath = Path.Combine(outputFolder.FullName, string.Format("Load_{0}{1}.sql", prefix, table.DatabaseObjectName)); Console.Write("."); System.IO.File.WriteAllText(sqlPath, sql); }