Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

Data comparison

Hi,

Good day to you.


1)I  have data feed in excel which I am converting to xml.And then I validate the xml data and insert data into sql server 2000.

2)Say If i am getting data for january  , My data feed will have data for january only.

But for February I will be getting the data both for jauray and february.For march I will be getting from january till march.SO this happenens for every month.SO the data feed will keep growing.

Questions for expert
------------------------
1)Since I am validating the data  in xml (as mentioned in step 1 above) Whats the best way I can Ignore the previous months record.Please note I am having data with 27000 records.

2)Then i have to update database ,SO whats the best way to ignore data not changed.

NOte this application is written in c#.net and sql server 2000.

Thanks for your help.

Narendra








0
NarendraNisha
Asked:
NarendraNisha
1 Solution
 
MogalManicCommented:
1) The XML validation is on the whole file, so I don't think you can validate part of the file.  Here are your options
       a. Split the file into two XML files
            1. Update.xml - Update data from Months from Januray to N-1 (don't validate)
            2. Insert.xml - Insert data for month N (Validate)
       b. Assuming that you are creating the XML through a well defined process, the XML will be valid.  This means that you are just validating the data.  If so, then just validate as you insert and abort the transaction on bad data:
(pseudocode)
    Read XML Data into Insert array and update array (could be any object actually)
    begin transaction
    try
          for each Row in data insert array
               Validate row data
                    If invalid, throw DataNotValidException()
               Insert Row data
          Commit data inserted
    catch All exceptions (SQL or validation)
          Rollback data
    finally
          end transaction

2) The problem is identifing the data that was updated in previous months
If your XML data has some some sort of transaction date it could be something like this:
For each Row in update data
    If updateDate>=1st of current month
        Update Row
For that matter this could be a solution to problem 1 too.  Only add to the XML if the record has changed in current month
For each Row in Excel spreadsheet
    If dataDate in current month
        add row to XML for insert
    else If updateDate>=1st of month
        Add row  to XML for update

If the XML does NOT have some sort of transaction date, you will just have to compare the data for differences.
You can compare in Oracle (probably slow)
    For each Row in Update XML
        UPDATE dataTable set a=xmlA, b=xmlB, c=xmlC
        WHERE keyCol=xmlKey and (a<>xmlA or b<>xmlB or c<>xmlC)
OR you could save the previous months XML data and compare the two files
Iterate OldData
Iterate NewData
While iterators not done
    oldData.next
    newData.next
    If oldData <> new Data
       Update row using new Data
end while
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now