Re: Comparing Data between Tables

On Wed, 16 Nov 2005 11:53:09 -0800, Randaddyz wrote:

>I messed up and now we have two tables that have slightly different
>information. What is a query that would allow me to look at the data
>differences between the two tables?

Hi Randaddyz,

You could use something like below to check for differences:

SELECT a.Column1, a.Column2, a.Column3, ..., a.ColumnN,
b.Column1, b.Column2, b.Column3, ..., b.ColumnN
FROM TableA AS a
FULL OUTER JOIN TableB AS b
ON a.Column1 = b.Column1
AND a.Column2 = b.Column2
AND a.Column3 = b.Column3
.......
AND a.ColumnN = b.ColumnN
WHERE a.ColumnX IS NULL
OR b.ColumnX IS NULL

Make sure to include ALL the tables columns in the ON clause.
Make sure that the column chossen for ColumnX does NOT allow NULLs.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
.

Relevant Pages

  • Re: need some SQL help
    ... hugo, do you know how I'd modify your query to return only a count of the ... Prev by Date: ...
    (comp.databases.theory)
  • Re: Query ... Distinct rows
    ... >I want to run a query that will return the distinct ORDER_ID that is Status ... Hi Wez, ... Best, Hugo ... Prev by Date: ...
    (microsoft.public.sqlserver.mseq)
  • Re: query plan in clear text
    ... you just have to create a query that does so. ... Please post DDL and all statements. ... > Best, Hugo ... Prev by Date: ...
    (comp.databases.ms-sqlserver)
  • Re: dbnull error with mysql....
    ... Thanks for the response. ... I am only returning one record with this query. ... >using a DataSet and see if the first record is in fact all Nulls. ... Prev by Date: ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Nearest Common Ancestor Report (XDb1s $1000 Challenge)
    ... Hugo Kornelis wrote: ... >>And then for a 10 million record database. ... >>this query would run, please. ... I don't have the money to spare on the kind of hardware that would be ...
    (comp.object)