Click here to Skip to main content
15,918,808 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
Hi All

I have two datatable
1. Master ( Actual data which is there in DB)
2. Child ( from another datasource)

In child table user might added new record, modified existing record in Master, or no change.

Need to loop through these two table and find
1. NEw record added - Means insert into Master table
2. Existing records modified - Means update the existing record in MAster table
3. NO change - Do nothing.

NOte : I have only Composite keys in master table
Thanks and Regards
Virkam
Posted
Comments
Kschuler 29-Mar-12 9:56am    
Which part are you having trouble with? What code do you have so far?
Vikramdcecse 29-Mar-12 10:45am    
I got the two source in two datatables , I am not sure about the looping logic for find the new records,updated records, no change.
Maciej Los 29-Mar-12 13:44pm    
What kind of engine: MS SQL, MS Access, ...?
Vikramdcecse 30-Mar-12 8:12am    
Master Table - Oracle
Child Table - Excel

RedGate's Schema Compare tool, SQL Compare Pro (but not standard edition), has a command line interface for automation. Ref:
http://www.red-gate.com/products/sql-development/sql-compare/[^]
 
Share this answer
 
Comments
Vikramdcecse 29-Mar-12 10:48am    
I want to do this in vb.net code. NOt using any tool as mentioned above
I would sort each table by one of the fields, the key field, and then walk down both tables

While (counterA < tableARecords && counterB < tableRecords)<br />
{<br />
  if (listA[counterA].Index == listA[CounterB].Index)<br />
  {<br />
    // compare records<br />
    counterA++;<br />
    counterB++<br />
  }<br />
  else if (listA[counterA].Index > listA[CounterB].Index)<br />
  {<br />
    //This means that a record in listA is not in listB<br />
    counterB++;<br />
  }<br />
  else<br />
  {<br />
   //This means that a record in listB is not in listA<br />
    counterA++;<br />
  }<br />
}<br />
//Deal with extra records in listA or listB here
 
Share this answer
 
The simple way to achieve that doesn't exist, a specially when more than one user can add, edit and delete data in the MS Excel file.
To compare data, you need to loop through all records in both tables (see solution 2 and 3).

If the count of records in your master table is less than few thousands, you can compare data on Excel level. Why?
1) To set ID for each equal record in MS Excel file and add "comparision date", and to eliminate it from future comparisions,
2) To improve future comparisions:
a) when user made changes in the existing record (with ID), need to clear "comparision date", to recognize it as "after changes",
b) when user added new record, should leave ID and "comparision date" field empty, to recognize it as a new record.
The rest of data are new or changed. What can i say? Hand-made work is waiting on you... ;(
Without knowing the structure of both tables and relationships between data we can't help you more...

The best way to avoid trouble is to write a program to communicate with the Oracle server.
 
Share this answer
 
Comments
Vikramdcecse 31-Mar-12 19:53pm    
Structure of the two tables remains same
1. FirstName
2. LastName
3. Department Working
4. Age
5. Comments1
6. comments2

I dont have primary key columns, rather than that composite keys, (FirstName+LastName+DepartmentWorking.
I dont have any date column as well
When you're wondering about looping logic, it's best to think about how you would do this if you had to work by hand with paper and pencil.

You'd start with the first master record. You'd run your check on that record. Then you'll need to check each of the child records that correspond to it. Once you're done you'll move on to the second master record.

I imagine your loops would look soemthing like this:

VB
'Loop the Master Table
For Each rowMaster as DataRow in dtMaster.Rows
   'Insert your code here that checks if the master row has been updated

   'Loop through this master record's child records
   For Each rowChild as DataRow in dtChild.Select("MasterIdFromChildTable=" & rowMaster("MasterIdFromMasterTable"))
      'Insert your code here that checks if the child row has been added
   Next
Next


You may have to arrange it so that any updating you do the Master table is done after all of the looping. The For Each may not work if you add/delete rows to the table you are looping and you may get an exception. Also, it will work faster if you wait to update everything at the end.

Hope this helps get you started.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900