Re: how to find differences between production and test databases


"Florence" <Florence@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7C5CF617-A44E-4BDF-8A87-8CD03A1B0B9F@xxxxxxxxxxxxxxxx
We have two SQLServer databases on different machines. One is our
production
database and the other our test/development database.

At the moment both databases have exactly the same metadata i.e. tables
and
columns.

The production database differs in a handful of rows. Some rows have been
inserted, deleted and a few modified.

How can we with a few SQL statements can I find out what rows have been
added and what rows have been modified between our test and production
databases?

I assume there is a PK.
For non-PK fields, doing a JOIN between both tables on the PK and specifying
test.fld1<> prod.fld1 etc in the WHERE clause

For PK fields, doing a LEFT-JOIN between both tables on the PK and looking
for NULL in the WHERE clause.
Missing rows present on 1st table and absent on 2nd table will show up.
Something like

SELECT t1.* FROM prod.dbo.table1 t LEFT JOIN test.dbo.table1 t1
ON t.fld1=t1.fld1 AND t.fld2=t1.fld2
WHERE t1.fld3 IS NULL

Stephen Howe



.

Relevant Pages

  • Re: custom data warehouse in python vs. out-of-the-box ETL tool
    ... Bill Inmon's "Building the Data Warehouse" is 17 ... My experience is that if you enjoy hacking around databases and are ... I had a server for each production DB, ...
    (comp.lang.python)
  • Re: custom data warehouse in python vs. out-of-the-box ETL tool
    ... I have an integrated system Dashboard project that I was going to ... My experience is that if you enjoy hacking around databases and are proficient in Python, than for small scale solutions it is preferable to do it yourself. ... I had a server for each production DB, a server that mirrored that production DB with some added columns per table for external synchronization purposes and a master synchronisation server. ...
    (comp.lang.python)
  • Re: MCP DMSII Program statistics
    ... important to know how programs are accessing databases in a production ... to poor data conversion. ... I have a daily run of SYSTEM/LOGGER showing me the processor hogs. ...
    (comp.sys.unisys)
  • Re: What is your database application development environment?
    ... development environment. ... Production - Test - Sandbox ... Most call it Dev, Test-QA, Production, but whatever you call it, this is the ... Dev databases are much smaller. ...
    (borland.public.delphi.non-technical)
  • Re: Compare data in tables of different structures
    ... Currently we have two Oralce 9i databases in the production ... We synchronize them *logically* every day. ... differences first only, not solve them at this stage. ... and we will migrate this system to a new one, but at this moment we have to solve this for the production people. ...
    (comp.databases.oracle.server)