Copy one table to another based on criteria [closed]

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhet开发者_开发百科orical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 11 years ago.

In my program below, I'm writing every record I select from MBRHISTDETL to a datatable. However, whenever a record is read from the MBRHISTDTL file, I'd like to perform more actions before writing it to the datatable. The way the program is written, though, it will write the whole resultset to the datatable when I only want particular records being written. How can I change this? What I'd like to do is this:

1. Select a record from MBRHISTDETL
2. Is it BILLTYPE 09 and is BILLMOYR <> 9999? If yes, continue. If not, get another record.
3. Use MBRNUM from MBRHISTDETL to get LOCATION, DISTRICT, and CYCLE from LOCINFODETL.
4. Does DISTRICT and CYCLE match the input parameters cbDistrict and cbCycle? If no, get another record.
5. Populate datatable
6. Go back to 1 unless end of file.

As my code is written right now, I can only complete steps 1 through 3 because I haven't figured out a way to grab a record from MBRHISTDETL and use that info to select another record from LOCINDODETL to verify the record I have is correct before writing to the datatable. Basically, I'm trying to get information from two different database files and write the information to a datatable. Am I going about this the right way?

Here's my code so far:

private void btnGo_Click(object sender, EventArgs e)
    {
        //get parameters
        string cycle = cbCycle.Text;
        string district = cbDistrict.Text;

        //create a connection to the database
        OdbcConnection DbConnection = new OdbcConnection("DSN=UPN2;uid=xxxx;pwd=xxxx");
        DbConnection.Open();

        //create a command to extract the required data and
        //assign it to the connection string
        OdbcCommand DbCommand = DbConnection.CreateCommand();
        DbCommand.CommandText = "SELECT * FROM CAV_MBRHISTDETL WHERE BILLTYPE = '09' " +
            "AND BILLMOYR <> '9999'";

        //Create a DataAdapter to run the command and fill the datatable
        OdbcDataAdapter da = new OdbcDataAdapter();
        da.SelectCommand = DbCommand;
        DataTable dt = new DataTable();

        //Put results into datatable.
        da.Fill(dt);


        tbOutput.Text = PrintDataTable(dt);

        DbCommand.Dispose();
        DbConnection.Close();

    }

---

If MBRNUM is a foreign key in LOCINFODETL then you only need the required MRBNUM from MBRHISDETL. Try a subselect:

SELECT MBRNUM, LOCATION, DISTRICT, CYCLE
from
    CAV_LOCINFODETL
WHERE
    MBRNUM IN (SELECT MBRNUM
               FROM CAV_MBRHISTDETL
               WHERE BILLTYPE = '09' AND BILLMOYR <> '9999')

---

If the tables are in the same database, you likely (depending on the database backend and configuration) could use an inner join to get all your data in a single query. For instance:

DbCommand.CommandText = 
 @"SELECT HIST.Field1, HIST.Field2, LOCINFO.Field3 FROM CAV_MBRHISTDEL AS HIST
   INNER JOIN LOCINFODETL AS LOCINFO ON HIST.MBRNUM = LOCINFO.MBRNUM 
   WHERE LOCINFO.CYCLE = @CYCLE AND
         LOCINFO.DISTRICT = @DISTRICT AND
         HIST.BILLTYPE = '09' AND
         HIST.BOLLMOYR <> '9999'";

DbCommand.Parameters.AddWithValue("@CYCLE", cycle);
DbCommand.Parameters.AddWithValue("@DISTRICT", district);

This takes care of your steps 1, 2, 3, 4, and 6, which all relate to selecting the data you need. Step 5 simply requires executing the command to fill a data table. (Note: replace Field1, Field2, etc. with the actual fields you need for the your data table, and LOCINFO.MBRNUM with LOCINFO.(whichever field in LOCINFODETL corresponds to MBRNUM).)

If the SQL query syntax is unclear, you may want to reference inner joins, parameterized queries, and table aliasing for more on the syntax.

---

Basically, I'm trying to get information from two different database files and write the information to a datatable

I assume that the database tables are in the same database. I would use a union in your query statement to link the two tables to a common key. there are many examples on how to create the relationship.