public string GenerateMergeSQL(string StagingTableSchema, string StagingTableName, string TargetTableSchema, string TargetTableName, SqlConnection _con, bool CheckSchemaDrift, Logging logging) { if (CheckSchemaDrift) { SqlCommand cmd = new SqlCommand(string.Format("Select * from {0}.{1}", StagingTableSchema, StagingTableName), _con); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable stagingDT = new DataTable(); da.Fill(stagingDT); cmd = new SqlCommand(string.Format("Select * from {0}.{1}", TargetTableSchema, TargetTableName), _con); da = new SqlDataAdapter(cmd); DataTable targetDT = new DataTable(); da.Fill(targetDT); bool schemaEqual = DataTableSchemaCompare.SchemaEquals(stagingDT, targetDT); if (schemaEqual == false) { logging.LogWarning(string.Format("****Schema Drift for Table {0}.{1} to {2}.{3}", StagingTableSchema, StagingTableName.Replace("#Temp_", ""), TargetTableSchema, TargetTableName.Replace("#Temp_", ""))); } } Table SourceTable = new Table { Name = StagingTableName, Schema = StagingTableSchema, PersistedCon = _con }; Table TargetTable = new Table { Name = TargetTableName, Schema = TargetTableSchema, PersistedCon = _con }; TargetTable.GetColumnsFromExistingDB(true); SourceTable.GetColumnsFromExistingDB(true); string PrimaryKeyJoin = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "a", "b", "=", " and ", true, true, false, false, false, null, false, false); string ColList = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "", "", "=", ",", true, true, false, false, true, null, true, false); string SelectListForInsert = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "b", "", "", ",", true, false, false, false, true, null, true, false); string InsertList = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "", "", "", ",", true, false, false, false, true, null, true, false); string UpdateClause = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "b", "", "=", ",", false, false, false, false, true, null, false, false); Dictionary <string, string> SqlParams = new Dictionary <string, string> { { "TargetFullName", TargetTable.SchemaAndName() }, { "SourceFullName", SourceTable.SchemaAndName() }, { "PrimaryKeyJoin_AB", PrimaryKeyJoin }, { "UpdateClause", UpdateClause }, { "SelectListForInsert", SelectListForInsert }, { "InsertList", InsertList } }; string MergeSQL = string.Empty; if (PrimaryKeyJoin.Length >= 4) { MergeSQL = GenerateSQLStatementTemplates.GetSQL(Shared.GlobalConfigs.GetStringConfig("SQLTemplateLocation"), "GenericMerge", SqlParams); } else { MergeSQL = GenerateSQLStatementTemplates.GetSQL(Shared.GlobalConfigs.GetStringConfig("SQLTemplateLocation"), "GenericTruncateInsert", SqlParams); } return(MergeSQL); }