Post reply

SQL to compare Access table with SQL table

  • October 31, 2011 at 10:18 am

    #259419

    Hello All,

    I am moving a few tables from MS Access 2007 to MS SQL Server 2008. Even though the table names and column names match exactly, there are a few changes in the datatypes. For e.g. Yes/No data types in Access have been created as smallint datatypes in SQL. This was done to retain values as is (Access stores -1 for True and 0 for False. If it is converted to a BIT, the corresponding values would become 1 and 0, respectively. This will cause issues at a lot of places in the application, so it was decided to retain the values as is).

    Now, here is my question. I am using SSIS to migrate this data. Once the data has been moved, I want to run some validation queries to identify any issues during data migration. The simplest one would be...

    SELECT src.ID, dest.ID, src.FirstName, dest.FirstName, src.LastName, dest.LastName,

    src.DOB, dest.DOB, src.SSN, dest.SSN

    FROM SourceTableInAccess src

    INNER JOIN DestinationTableInSQL dest ON src.ID = dest.ID

    WHERE src.FistName <> dest.FirstName OR src.LastName <> dest.LastName OR

    src.DOB <> dest.DOB OR src.SSN <> dest.SSN

    But, the above query assumes that ID column has been loaded correctly.

    In a nutshell, I am looking for a query that will find out any disparities between 2 tables (mismatching column data and/or mismatching rows).

    Is there a feature/control in SSIS that can be used for this?

    Your help on this topic will be grately appreciated.

    Regards

  • October 31, 2011 at 2:22 pm

    #1401301

    To my knowledge there is not a transform in SSIS that would do this sort of field by field comparison natively, easily. You could use a Conditional Split comparing every column in both tables much as you do in your sample SQL.

    In T-SQL, since I expect that each comparison would be a one time deal, I would probably do something like this to save typing.

    SELECT *

    FROM DataSourceTable

    EXCEPT

    SELECT *

    FROM DataDestinationTable

    This will show every row in the DataSourceTable that does not have an exact match in the destination. You might also want to reverse the order to see if anything snuck into the DataDestinationTable that shouldn't have.

  • November 2, 2011 at 2:57 pm

    #1402427

    Thanks, Daniel.

Viewing 3 posts - 1 through 2 (of 2 total)

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