Compare data in two tables

Is there an easy way (using SQL) to compare two tables
with the same structure? Basically if one piece of data
is different than the other (based on the primary key),
print the row.See http://www.red-gate.com/. Their SQL Data Compare is an excellent tool.
If you are a business, it's decently priced. For an individual, it may be a
little pricy ($195+).
Another way is to write a query that joins the two tables by the primary key
(use FULL OUTER JOIN) and then has a REALLY big WHERE clause that compares
each of the fields.
For example,
create table data (and data2)
(
pk int not null constraint primary key,
str varchar(50),
num int
)
select
data.pk as data_pk,
data.str as data_str,
data.num as data_num,
data2.pk as data2_pk,
data2.str as data2_str,
data2.num as data2_num,
from
-- full outer join will get records on either side even when join
condition fails
data full outer join data2 on data.pk = data2.pk
where
-- if either PK is null, the records are different (i.e. the join failed
thus no matching record)
data.pk is null
or
data2.pk is null
or
-- check each field...can be tedious;
-- the ISNULL stuff is a cheesy way to deal with possible NULLs in fields
and still have them compare
-- a better way for comparing NULLs is:
-- ((fielda is null and fieldb is not null) or (fielda is not null and
fieldb is null) or (fielda <> fieldb))
isnull(data.str, '###BOGUS###') <> isnull(data2.str, '###BOGUS###')
or
isnull(data.num, -999999999) <> isnull(data.num, -999999999)
This can be very tedious is you are comparing many tables. If you wanted
something more dynamic, you could create a stored procedure that dynamically
created the above SQL for any two tables using the metadata in SYSOBJECTS
and SYSCOLUMNS.
"Amy" <anonymous@.discussions.microsoft.com> wrote in message
news:16d101c536c5$69fb47e0$a601280a@.phx.gbl...
> Is there an easy way (using SQL) to compare two tables
> with the same structure? Basically if one piece of data
> is different than the other (based on the primary key),
> print the row.|||a UNION of the tables would tell of you if the two tables are the same.
Also, you could use NOT EXISTS clause to print those records that do not
match or you could use
LEFT JOIN and select only those columns that have a NULL value.
Gopi
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:Ob8xdlsNFHA.204@.TK2MSFTNGP15.phx.gbl...
> See http://www.red-gate.com/. Their SQL Data Compare is an excellent
> tool.
> If you are a business, it's decently priced. For an individual, it may be
> a
> little pricy ($195+).
> Another way is to write a query that joins the two tables by the primary
> key
> (use FULL OUTER JOIN) and then has a REALLY big WHERE clause that compares
> each of the fields.
> For example,
> create table data (and data2)
> (
> pk int not null constraint primary key,
> str varchar(50),
> num int
> )
> select
> data.pk as data_pk,
> data.str as data_str,
> data.num as data_num,
> data2.pk as data2_pk,
> data2.str as data2_str,
> data2.num as data2_num,
> from
> -- full outer join will get records on either side even when join
> condition fails
> data full outer join data2 on data.pk = data2.pk
> where
> -- if either PK is null, the records are different (i.e. the join failed
> thus no matching record)
> data.pk is null
> or
> data2.pk is null
> or
> -- check each field...can be tedious;
> -- the ISNULL stuff is a cheesy way to deal with possible NULLs in fields
> and still have them compare
> -- a better way for comparing NULLs is:
> -- ((fielda is null and fieldb is not null) or (fielda is not null and
> fieldb is null) or (fielda <> fieldb))
> isnull(data.str, '###BOGUS###') <> isnull(data2.str, '###BOGUS###')
> or
> isnull(data.num, -999999999) <> isnull(data.num, -999999999)
> This can be very tedious is you are comparing many tables. If you wanted
> something more dynamic, you could create a stored procedure that
> dynamically
> created the above SQL for any two tables using the metadata in SYSOBJECTS
> and SYSCOLUMNS.
> "Amy" <anonymous@.discussions.microsoft.com> wrote in message
> news:16d101c536c5$69fb47e0$a601280a@.phx.gbl...
>|||On Fri, 1 Apr 2005 09:48:36 -0500, Mike Jansen wrote:
(snip)
> -- the ISNULL stuff is a cheesy way to deal with possible NULLs in fields
>and still have them compare
> -- a better way for comparing NULLs is:
> -- ((fielda is null and fieldb is not null) or (fielda is not null and
>fieldb is null) or (fielda <> fieldb))
> isnull(data.str, '###BOGUS###') <> isnull(data2.str, '###BOGUS###')
> or
> isnull(data.num, -999999999) <> isnull(data.num, -999999999)
Hi Mike,
Here's another way to compare two columns that might contain NULLS - one
that can be used if there is no reliable bogus value:
WHERE NULLIF(column1, column2) IS NULL
AND NULLIF(column2, column1) IS NULL
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I just tested that now and it seems like NULLIF blows chunks if the first
parameter is NULL, making it not usable if the data can contain NULLs.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:fbbr41lqgaurdc00m07gvn5ufi6kr6qvmk@.
4ax.com...
> On Fri, 1 Apr 2005 09:48:36 -0500, Mike Jansen wrote:
> (snip)
fields
and
> Hi Mike,
> Here's another way to compare two columns that might contain NULLS - one
> that can be used if there is no reliable bogus value:
> WHERE NULLIF(column1, column2) IS NULL
> AND NULLIF(column2, column1) IS NULL
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 1 Apr 2005 15:45:33 -0500, Mike Jansen wrote:

>I just tested that now and it seems like NULLIF blows chunks if the first
>parameter is NULL, making it not usable if the data can contain NULLs.
Hi Mike,
That's why you have to use both lines:
The first NULLIF expression will return NULL if column1 and column2 are
not NULL and equal, or when column1 is NULL.
The first NULLIF expression will return NULL if column1 and column2 are
not NULL and equal, or when column2 is NULL.
If both NULLIF expressions return NULL, then either column1 and column2
are not NULL and equal, or column1 and column2 are both NULL.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||No, I mean it BLOWS CHUNKS. It errors out. You can't use it. Try this in
SQL Query Analyzer
SELECT NULLIF(null, 1)
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:rpdr41tnjn71e03kvqatc5o96lo85aetft@.
4ax.com...
> On Fri, 1 Apr 2005 15:45:33 -0500, Mike Jansen wrote:
>
> Hi Mike,
> That's why you have to use both lines:
>
> The first NULLIF expression will return NULL if column1 and column2 are
> not NULL and equal, or when column1 is NULL.
> The first NULLIF expression will return NULL if column1 and column2 are
> not NULL and equal, or when column2 is NULL.
> If both NULLIF expressions return NULL, then either column1 and column2
> are not NULL and equal, or column1 and column2 are both NULL.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 1 Apr 2005 16:13:13 -0500, Mike Jansen wrote:

>No, I mean it BLOWS CHUNKS. It errors out. You can't use it. Try this in
>SQL Query Analyzer
>SELECT NULLIF(null, 1)
Hi Mike,
I''ve never tried that until now - you're right, you can't use the
literal NULL in a NULLIF expression.
However, this works:
declare @.a int, @.b int
set @.a = null
set @.b = 1
SELECT NULLIF(@.a, @.b)
And this works as well
select
data.pk as data_pk,
data.str as data_str,
data.num as data_num,
data2.pk as data2_pk,
data2.str as data2_str,
data2.num as data2_num,
from
-- full outer join will get records on either side even when join
condition fails
data full outer join data2 on data.pk = data2.pk
where
-- if either PK is null, the records are different (i.e. the join
failed thus no matching record)
data.pk is null
or
data2.pk is null
or
-- check each field...can be tedious;
(NULLIF (data.str, data2.str) IS NULL
AND NULLIF (data2.str, data.str) IS NULL))
or
(NULLIF (data.num, data2.num) IS NULL
AND NULLIF (data2.num, data.num) IS NULL))
(Which brings us back on topic)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Guess I didn't do thorough enough testing :) Since in this case a literal
NULL would never be the issue, it would work.
Thanks for info.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:55fr415hebp87nk87qg96bfv2dcmvlqugd@.
4ax.com...
> On Fri, 1 Apr 2005 16:13:13 -0500, Mike Jansen wrote:
>
in
> Hi Mike,
> I''ve never tried that until now - you're right, you can't use the
> literal NULL in a NULLIF expression.
> However, this works:
> declare @.a int, @.b int
> set @.a = null
> set @.b = 1
> SELECT NULLIF(@.a, @.b)
> And this works as well
> select
> data.pk as data_pk,
> data.str as data_str,
> data.num as data_num,
> data2.pk as data2_pk,
> data2.str as data2_str,
> data2.num as data2_num,
> from
> -- full outer join will get records on either side even when join
> condition fails
> data full outer join data2 on data.pk = data2.pk
> where
> -- if either PK is null, the records are different (i.e. the join
> failed thus no matching record)
> data.pk is null
> or
> data2.pk is null
> or
> -- check each field...can be tedious;
> (NULLIF (data.str, data2.str) IS NULL
> AND NULLIF (data2.str, data.str) IS NULL))
> or
> (NULLIF (data.num, data2.num) IS NULL
> AND NULLIF (data2.num, data.num) IS NULL))
> (Which brings us back on topic)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||This works:
SELECT NULLIF(1, null)
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:55fr415hebp87nk87qg96bfv2dcmvlqugd@.
4ax.com...
> On Fri, 1 Apr 2005 16:13:13 -0500, Mike Jansen wrote:
>
> Hi Mike,
> I''ve never tried that until now - you're right, you can't use the
> literal NULL in a NULLIF expression.
> However, this works:
> declare @.a int, @.b int
> set @.a = null
> set @.b = 1
> SELECT NULLIF(@.a, @.b)
> And this works as well
> select
> data.pk as data_pk,
> data.str as data_str,
> data.num as data_num,
> data2.pk as data2_pk,
> data2.str as data2_str,
> data2.num as data2_num,
> from
> -- full outer join will get records on either side even when join
> condition fails
> data full outer join data2 on data.pk = data2.pk
> where
> -- if either PK is null, the records are different (i.e. the join
> failed thus no matching record)
> data.pk is null
> or
> data2.pk is null
> or
> -- check each field...can be tedious;
> (NULLIF (data.str, data2.str) IS NULL
> AND NULLIF (data2.str, data.str) IS NULL))
> or
> (NULLIF (data.num, data2.num) IS NULL
> AND NULLIF (data2.num, data.num) IS NULL))
> (Which brings us back on topic)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)