Finding difference in two databases/tables

  • I have compared two tables with the same data in two different databases(aslo the schema) with SSIS(Comparrison), sql attached. The Schema comparison return an exact match, the table quite a few new updates.

    The tables are from Production and QA databases. I ran the exact queries on both of these databases, see attached. I am trying to find the difference in the Production database that is not populating table "auditLevyBreakdown". This table needs to have data in it for reporting purposes. Please assist?

    You must be logged in to view attached files.
  • Well you shouldn't compare data in QA to data in Production, the two data sets will be entirely different, also you shouldn't have production data in any other environment than production, you should be scrubbing the data and only having test data in your lower environments.


    It looks like your missing dbo.fn_getsummaryreport from production, I would check what that does in QA, but if you have checked the schema for any differences and there are no other triggers, procedures etc which would directly insert into auditLevyBreakdown, then the issue will come from higher up the application stack.


    You would need to look at SSIS packages, application code, external scripts, agent jobs etc etc etc the list is endless, to what should be inserting into auditLevyBreakdown and debug why it isn't happening.

  • Your feedback is in contractdiction of what i have been told in terms of comparing production with qa. It stems from this issue:

    • This reply was modified 5 months, 3 weeks ago by  yrstruly.
    You must be logged in to view attached files.
  • Well Jeff there is referring to static data.  Do you know if the data in that table is static data or is it dynamic data?

    Comparing data in QA to Production shouldn't be a thing ideally as you should be scrubbing anything which could be a data breach violation from the database prior to moving it to QA/UAT/DEV/TEST, as your opening yourself up for data breaches, HIPPA GDPR violations etc.

    So anything names, addresses, emails, phone numbers, zip codes, anything which can personally identify anybody should be scrubbed to test data prior to moving to lower environments, so you cannot compare your customer table in QA to the customer table in production as the data wouldn't be a match as an example.

    Also as the data drifts through regular QA routines again the data in these dynamic tables will not match your production databases, so you should really not compare QA to Production in terms of data, unless you have a solid data dictionary which details what table stores what information and if that table is static / configuration data or if it is tied to the daily OLTP processes etc.


    Comparing the schema on the other hand is totally OK as yes you could end up with schema drift should the deployments be manual or something hasn't made its way to production yet, but schema <> data so yeah schema is OK, data is generally not OK unless you know the data.

  • Im not sure about Static/Dynamic. I requested what it is, and was informed it is Static(or so this person guess).

  • OK at a guess then, on your local copy of the production data, insert the missing data as it is in the QA copy to the production copy and verify if this indeed fixes the issue you have noticed at least locally on your local copy.


    If it does then you can take an educated guess that the data is static and as such you must also insert that data manually into the real production copy of the database.

  • Where do you suggest i start?

    You must be logged in to view attached files.
  • You would need to write a script to insert the two rows from QA copy auditLevyBreakdown into your prod copy auditLevyBreakdown table.

    Or you may use the import/export data wizard instead.

  • You mean the DDL like it is, see attached?

    You must be logged in to view attached files.
  • No it will be DML.

    INSERT INTO dbo.auditLevyBreakdown VALUES
    (row one data),
    (row two data)
  • That is the problem, i just cant find that DML.

    • This reply was modified 5 months, 3 weeks ago by  yrstruly.
    You must be logged in to view attached files.
  • It’s not going to be in the database solution as that’s your schema only.

    You have the data in the QA database just copy that and convert it to an insert statement and test it or use the import export data wizard to copy it from QAcopy to ProdCopy

  • I dont see any procedure in QA that does this "INSERT INTO dbo.auditLevyBreakdown VALUES

    (row one data),

    (row two data)"

  • You need to write it yourself.

    If that data is static someone manually wrote a script to insert it as a one time operation into QA, it is not stored in your schema anywhere.

    Copy the two rows from QA and paste them into a new query then wrap it all in the right syntax to turn them into DML insert statements.

  • Or just copy auditLevyBreakdown from QA to Production, like you said?

Viewing 15 posts - 1 through 15 (of 33 total)

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