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); }
private static void CreateDataMartView(DatabaseObject table, DirectoryInfo dirDimViews, string viewSchema, string columnList, string templateName) { CreateDataMartView(table, dirDimViews, viewSchema, columnList, templateName, null); }
private static void CreateDataTranslationViews(DatabaseInfo database, DatabaseObject table, DirectoryInfo dirViews) { // clean up table names here NamingCleanup.CleanDatabaseObjectName(table); StringBuilder sb = new StringBuilder("create view ["); sb.Append(table.StagingAreaSchema); sb.Append("].[dt_"); sb.Append(table.DatabaseObjectName); sb.AppendLine("]"); sb.AppendLine("as"); sb.AppendLine(TemplateComponents.AutogenWarning()); sb.AppendLine("select"); // add LoadLogId column sb.AppendLine("\t-- system fields"); sb.AppendLine("\t[LoadLogId],"); // sb.AppendLine("\t[DatabaseInfoId],"); // sb.AppendLine("\t[DatabaseGroupId],"); sb.AppendLine(); sb.AppendLine("\t-- staging table fields"); bool addComma = false; //foreach (DatabaseColumn column in table.Columns) //{ // if (table.StagingAreaSchema.StartsWith("stgX")) // { // // clean up Quantum column names here // NamingCleanup.CleanColumnNames(column); // } //} foreach (DatabaseColumn column in table.Columns.Where(x => x.UseColumn).OrderBy(x => x.DatabaseColumnName)) { if (addComma) { sb.AppendLine(","); } if (column.DataType == column.TargetDataType && column.Length == column.TargetLength && column.Precision == column.TargetPrecision && column.Scale == column.TargetScale) { // no cast to apply sb.AppendLine(); sb.Append("\t-- no cast from "); sb.Append(column.DataType.ToLower()); sb.Append(column.GetDataTypeModifiers(false, false)); AddMaxLength(sb, column); sb.AppendLine(); switch (column.TargetDataType.ToLower()) { case "nvarchar": case "nchar": case "varchar": case "char": sb.Append("\t"); TrimStringAsRequired(sb, column); break; default: sb.Append("\t["); sb.Append(column.DatabaseColumnName); sb.Append("]"); break; } } else { // we have a specific cast to apply sb.AppendLine(); sb.Append("\t-- was "); sb.Append(column.DataType.ToLower()); sb.Append(column.GetDataTypeModifiers(false, false)); AddMaxLength(sb, column); sb.AppendLine(); sb.Append("\t"); switch (column.TargetDataType.ToLower()) { case "nvarchar": case "varchar": case "nchar": case "char": if (column.DataType == "text") { // source data type is text, so first do a cast e.g. // cast(rtrim(ltrim(left(cast([notes_text] as [varchar](500)) ,500))) as [varchar](500)) as [NotesText] sb.Append("cast(rtrim(ltrim(left(cast(["); sb.Append(column.DatabaseColumnName); sb.Append("] as ["); sb.Append(column.TargetDataType); sb.Append("]("); sb.Append(column.TargetLength); sb.Append(")),"); sb.Append(column.TargetLength); sb.Append("))) as ["); sb.Append(column.TargetDataType); sb.Append("]("); sb.Append(column.TargetLength); sb.Append("))"); } else { TrimStringAsRequired(sb, column); } break; case "bit": if (column.DataType.ToLower() == "tinyint") { if (column.MaxValueInTable <= 1) { ReplaceTinyIntWithBit(sb, column); } else { // leave as tinyint (no cast) sb.Append("["); sb.Append(column.DatabaseColumnName); sb.Append("]"); } } else { ReplaceTinyIntWithBit(sb, column); } break; case "decimal": case "numeric": sb.Append("cast(["); sb.Append(column.DatabaseColumnName); sb.Append("] as "); sb.Append(column.TargetDataType); sb.Append("("); sb.Append(column.Precision); sb.Append(","); sb.Append(column.Scale); sb.Append("))"); break; default: sb.Append("cast(["); sb.Append(column.DatabaseColumnName); sb.Append("] as "); sb.Append(column.TargetDataType); sb.Append(")"); break; } } sb.Append(" as ["); sb.Append(column.TargetColumnName); sb.Append("]"); addComma = true; } sb.AppendLine(""); sb.Append("from "); sb.Append(table.StagingAreaSchema); sb.Append(".["); sb.Append(table.DatabaseObjectName); sb.Append("]"); sb.AppendLine(";"); string sqlPath = Path.Combine(dirViews.FullName, table.DatabaseObjectName + ".sql"); //Console.WriteLine("Writing View {0}", sqlPath); Console.Write("v"); System.IO.File.WriteAllText(sqlPath, sb.ToString()); }