Compare data between two views on two different databases

How can I compare data returned by two views on two different
databases? I want to compare column by column or the whole dataset. I
need to do that in query.
Thanks"Sehboo" <MasoodAdnan@.gmail.com> wrote in message
news:1178058288.926031.204610@.h2g2000hsg.googlegroups.com...
> How can I compare data returned by two views on two different
> databases? I want to compare column by column or the whole dataset. I
> need to do that in query.
> Thanks
>
It's not difficult to compare them but you haven't specified exactly what
result you would like to see from the comparison. Maybe you just want the
exceptions - the rows that don't appear in both views. In that case:
(SELECT *
FROM DB01.dbo.view1
EXCEPT
SELECT *
FROM DB02.dbo.view2)
UNION
(SELECT *
FROM DB02.dbo.view2
EXCEPT
SELECT *
FROM DB01.dbo.view1);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the quick response, but it doesn't work for me - hopefully
it is just me.
If I run this on sql query analyzer
Except({Canada, [British Columbia], Mexico, [British Columbia],
USA,
Washington}, {Canada, Mexico, California})
I get [Microsoft][ODBC SQL Server Driver]Syntax error or access
violation
I think Except only works for enterprise version of sql server.
And yes, by the way, I just want the exceptions - the rows that don't
appear in both views.
On May 1, 6:37 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> "Sehboo" <MasoodAd...@.gmail.com> wrote in message
> news:1178058288.926031.204610@.h2g2000hsg.googlegroups.com...
>
>
> It's not difficult tocomparethem but you haven't specified exactly what
> result you would like to see from the comparison. Maybe you just want the
> exceptions - the rows that don't appear in both views. In that case:
> (SELECT *
> FROM DB01.dbo.view1
> EXCEPT
> SELECT *
> FROM DB02.dbo.view2)
> UNION
> (SELECT *
> FROM DB02.dbo.view2
> EXCEPT
> SELECT *
> FROM DB01.dbo.view1);
> --
> David Portas,SQLServerMVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version ofSQLServeryou are using and specify the content
> of any error messages.
> SQLServerBooks Online:http://msdn2.microsoft.com/library/ms130214(en-US,SQ
L.90).aspx
> --|||"Sehboo" <MasoodAdnan@.gmail.com> wrote in message
news:1178062348.162726.300260@.y80g2000hsf.googlegroups.com...
> Thanks for the quick response, but it doesn't work for me - hopefully
> it is just me.
> If I run this on sql query analyzer
> Except({Canada, [British Columbia], Mexico, [British Columbia
], USA,
> Washington}, {Canada, Mexico, California})
> I get [Microsoft][ODBC SQL Server Driver]Syntax error or access
> violation
Correct. But that's not the query I gave you or anything like it! Why did
you add the extra parentheses and remove the SELECT statements? Please
consult Books Online if you need help with the syntax.

> I think Except only works for enterprise version of sql server.
>
It works in all editions but only in version 2005. Always state what version
you are using. Following is another alternative that should be compatible
with 2000 as well. If you need more assistance it would help if you could
post DDL and sample data to reproduce the problem.
SELECT key_col, col1, col2, col3
FROM
(SELECT
COALESCE(v1.key_col, v2.key_col),
COALESCE(v1.col1, v2.col1),
COALESCE(v1.col2, v2.col2),
COALESCE(v1.col3, v2.col3)
FROM DB01.dbo.view1 AS v1
FULL JOIN DB02.dbo.view2 AS v2
ON v1.key_col = v2.key_col
WHERE v1.key_col IS NULL OR v2.key_col IS NULL)
AS t(key_col, col1, col2, col3);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--