Checking if a row exists and if it does, has it changed

  • Hi,

    I have an old db and a new db. The table structures have changed alot since the new db has been normalised.

    I need to use one tool to perform both data migration and data comparison. A few days after the migration completes i need to check if data in the old db has changed- if it's different to data in the new db.

    If yes, then i have to write the changes into a log file and not update the new database.

    What is the best tool to perform both data migration and data comparison?

    Will SSIS help me achieve both results. If yes, how? Is there some sample that i can learn from?

    Also, should i first import the data from the old db to a temp db with the new db structure and than to the new db and perform the data comparison on the temp db?

    Thanks

  • Let me make myself more clear 🙂

    The primary keys are different in the 2 dbs. So checksum won't do the trick here.

    We don’t have control over the old application and old db. We will just have access to it for migration and comparison purposes.

    When the old db is updated than the new db should also be updated. The team leader wants to compare the data to know that the new db gets the data correctly and we end up with the same results for the product. Later the old db won’t be used anymore.

    The differences have to be logged into a readable file.

    I think SSIS is fine for migration but will it also help in the comparison?

    Thanks

  • I'm not sure that there is a tool out there that will do what you want. Any comparison tool is going to be looking to compare the same schemas.

    One thing you could do is to create views in the new db that match the tables in the old db then you could do a comparison using linked servers and EXCEPT.

  • now many columns and rows are in the tables you wish to compare?

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • There is not an out-of-the-box solution for you. You can build your migration out in SSIS or just script it in T-SQL. Regarless of the method, you'll be stuck with coding out your comparison with whichever tool you choose.

    By the way, you said that your PK's are different between systems, but I'm sure you still have a way to uniquely identify an entity from one system to the next, right?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • Oh, and as far as auditing new changes in your old system, can't you run a SQL trace to capture the INSERTs/UPDATEs/DELETEs?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply