コード例 #1
0
    protected void UpdateData(string db, string tbl, string schema)
    {
        string cols = sql.GetAllColumnsInTable(db, tbl, "dbo");
        string pk   = sql.GetPKForTable(db, tbl, "dbo");

        string insSQL = "INSERT INTO uwacdb." + schema + "." + tbl + " (" + cols + ") " +
                        " select " + cols + " from " + db + ".." + tbl + " where " + pk + " not in " +
                        " (select " + pk + " from  uwacdb." + schema + "." + tbl + " )";


        string delSQL = "DELETE FROM uwacdb." + schema + "." + tbl +
                        "  where " + pk + " not in " +
                        " (select " + pk + " from  " + db + ".dbo." + tbl + " )";


        string updateCols = sql.GetUpdateSQLForAllColumnsInTable(db, tbl, "dbo", pk);
        string updatePKs  = sql.GetPKsOfUnequalRecords(db, tbl, schema);

        string updSQL = "UPDATE uwacdb." + schema + "." + tbl + " set " +
                        updateCols + " from " + db + ".dbo." + tbl + " b" +
                        " where uwacdb." + schema + "." + tbl + "." + pk + " = b." + pk +
                        " AND b." + pk + " IN (" + updatePKs + ")";


        sql.NonQuery_from_SQLstring("SET IDENTITY_INSERT uwacdb." + schema + "." + tbl + " ON");
        sql.NonQuery_from_SQLstring(insSQL);
        sql.NonQuery_from_SQLstring("SET IDENTITY_INSERT uwacdb." + schema + "." + tbl + " OFF");
        sql.NonQuery_from_SQLstring(delSQL);

        sql.NonQuery_from_SQLstring("DISABLE TRIGGER ALL ON " + schema + "." + tbl);
        sql.NonQuery_from_SQLstring(updSQL);
        sql.NonQuery_from_SQLstring("ENABLE TRIGGER ALL ON " + schema + "." + tbl);

        // lblInfo.Text = updateCols;
    }