compare 2 tables in 2 databases

how do you compare a table in 1 database to antoher table
in another datbase?Jamie,
What exactly are you trying to compare? Schema? Data? There are a number
of products that do each.
J.R.
Largo SQL Tools
The Finest Collection of SQL Tools Available
http://www.largosqltools.com
"Jamie Elliott" <jelliott@.alexlee.com> wrote in message
news:12c101c38cd7$a801bf90$a101280a@.phx.gbl...
> how do you compare a table in 1 database to antoher table
> in another datbase?|||There's a good 3rd party tool called SQLCompare you might find useful.
Alternitavly just run sp_help tablename
against both tables
--
HTH
Ryan Waight, MCDBA, MCSE
"Jamie Elliott" <jelliott@.alexlee.com> wrote in message
news:12c101c38cd7$a801bf90$a101280a@.phx.gbl...
> how do you compare a table in 1 database to antoher table
> in another datbase?|||its kind of a missing leading question...Im not really
trying to compare...I need a query statement to extract
data from a table in one database and other data in
another table in a different database...basically I'm
trying to keep from doubling up on data...justing need
the syntax to look at data from database1.table1.fielda
and database2.table2.fieldb....I figure there is a simple
answer to this but I am not a query guru and I apologize
for the lack of knowledge, but any help would be wonderful
and greatly appreciated...|||Hi Jamie,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you on your issue.
First of all, I would like to thank J.R and Ryan for their kind help. If you just want to compare the
data in the table, you can use 3rd part tools to help compare the difference of the table. The
SQL Compare tool is available on:
http://www.programmersheaven.com/zone18/cat1028/17861.htm
http://www.mofunzone.com/downloadsoftware/500000018634.shtml
From your description, it seems you do not really want to compare the data. What your
concern is to extract the data from two tables in different database and avoid duplicated data.
Do I fully understand you? If there is anything I misunderstand, please feel free to let me know.
To perform like that, you can use JOIN clause like:
SELECT * FROM database1..table1 AS a INNER JOIN database2..table2 AS b
ON a.fielda = b.fieldb
# Note: There are two dots (..) between DatabaseName and TableName
For detailed information on JOIN clause, please reference SQL Server Books Online
searching on the topic of "Using Joins".
You can also manually compare the difference of the table by executing stored procedure
SP_HELP in the Query Analyzer (QA) like:
Use database1
go
SP_HELP table1
go
Use database2
go
SP_HELP table2
G
and view the difference of columns in QA.
Jamie, do I answer your question? Please let me know if my suggestion helps you resolve the
problem. If there is anything more I can assist you, please feel free to let me know.
Regards,
Billy Yao
Microsoft Online Partner Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Jamie,
Has your problem resolved by using 3rd part tools recommended from J.R and Ryan, and
following my suggestions to perform this comparison and successfully extract data from
tables in different databases? Please leave a message in newsgroup to let us know if you
manage to perform what you want or whether our suggestion helps. If there is anything we can
assist, please feel free to let us know.
Frankly speaking, I'm not really sure what your ultimate concern and from you description, I
have little idea of your exact performance on this comparison. Could you specify what you
really concern (current status, what you want/think to do, the results you'd like to obtain and the
purpose of performing (comparison) like that)?
Consequently, I'd like you post the two tables' DDL and some helpful sample data and the
results he wants. Otherwise, it is really difficult to guess the details and provide the solutions.
Anything more you can add is greatly appreciated.
Regards,
Billy Yao
Microsoft Online Partner Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.