Re: how to find differences between production and test databases
- From: "Stephen Howe" <stephenPOINThoweATtns-globalPOINTcom>
- Date: Mon, 13 Nov 2006 14:49:05 -0000
"Florence" <Florence@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7C5CF617-A44E-4BDF-8A87-8CD03A1B0B9F@xxxxxxxxxxxxxxxx
We have two SQLServer databases on different machines. One is ourproduction
database and the other our test/development database.and
At the moment both databases have exactly the same metadata i.e. tables
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
.
- Prev by Date: Re: ADO Recordset to SQLite
- Next by Date: Re: ADO Recordset to SQLite
- Previous by thread: Re: how to find differences between production and test databases
- Index(es):
Relevant Pages
|
|
