public void AutoBulkInsertAndMerge(DataTable dt, string StagingTableName, string TargetTableName)
        {
            TaskMetaDataDatabase TMD = new TaskMetaDataDatabase();

            using (SqlConnection conn = TMD.GetSqlConnection())
            {
                Table SourceTable = new Table
                {
                    Name         = StagingTableName,
                    Schema       = null,
                    PersistedCon = conn
                };


                Table TargetTable = new Table
                {
                    Name         = TargetTableName,
                    Schema       = "dbo",
                    PersistedCon = conn
                };
                TargetTable.GetColumnsFromExistingDB(true);

                TMD.BulkInsert(dt, SourceTable, true, conn);
                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.QuotedSchemaAndName() },
                    { "SourceFullName", SourceTable.QuotedSchemaAndName() },
                    { "PrimaryKeyJoin_AB", PrimaryKeyJoin },
                    { "UpdateClause", UpdateClause },
                    { "SelectListForInsert", SelectListForInsert },
                    { "InsertList", InsertList }
                };


                string MergeSQL = GenerateSQLStatementTemplates.GetSQL(Shared.GlobalConfigs.GetStringConfig("SQLTemplateLocation"), "GenericMerge", SqlParams);

                conn.Execute(MergeSQL);
            }
        }
        public string GenerateInsertSQL(string StagingTableSchema, string StagingTableName, string TargetTableSchema, string TargetTableName, SqlConnection _con)
        {
            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 = GenerateSQLStatementTemplates.GetSQL(Shared.GlobalConfigs.GetStringConfig("SQLTemplateLocation"), "GenericInsert", SqlParams);

            return(MergeSQL);
        }
        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);
        }