comparing two tables with same structure and different names in two different databases on two different servers
-
Does any one know how to compare two tables with same structure and different names in two different databases on two different servers. Following is the sample to my question.
In Server1 there is a table called Table1 with the columns Col1 int; Col2 varchar(20)
In Server2 there is a table called Table2 with the columns Col1 int; Column2 varchar(20)
Both the tables have the same data and same number of columns and same structures. Difference is they are with different names. Is there any way to compare them. PLEASE SUGGEST me. This is very very urgent in my present work...
Thanks,
Sai
-
You might try the compare tools from Red Gate and Apex SQL to see if they handle this.
-
Steve, none currently offers managed compares like this. They expect comparison based on object name.
-
You are correct. Redgate and other SPs related to comparison were built with the base of the same table name. I'm looking for different table names. Please help me, if anyone has a solution for this.
-
You could build something with SQL DMO if you want. I have some example code of getting the Scripts and the object types and sizes if you want to take a look. But I haven't started writing my own comparison tools because I haven't had a specific unmet need yet like yours.
-
Sure. I'm eager to look at them.
-
According to the example, not only does the object names differ, but also column names - which leaves the equality test to just be about columnorder and datatype match..?
If so, how far should one take this?
Is tab1 ( col1 int not null, col2 char(10) not null )
the same as table1 ( column1 int not null, column2 char(10) not null) ..?
Or, what about tab1 ( col2 char(10) not null, col1 int not null ) - is this the "same" as the first table above? (It is by realtional standards, since order in definition has no bearing..)
Could get pretty messy I think...
/Kenneth
-
Hallo!
How do I check equality of tables:
1. Simplest. COUNT(*) AND UNION
SELECT
Count1 = (SELECT COUNT(*) FROM Server1.db1.dbo.Table1)
,Count2 = (SELECT COUNT(*) FROM Server2.db2.dbo.Table2)
,CountUnion = (
SELECT COUNT(*) FROM
(
SELECT
Col1
,Col2
FROM
Server1.db1.dbo.Table1
UNION
SELECT
Col1
,Col2 = Column2 -- You wrote that field name differs
FROM
Server1.db1.dbo.Table1
)
)
Very simple. Very slow. Very reliable.
When I need to see lines that differs - I write UNION with SELECTs against each table and NOT EXISTS in WHERE statement.
2.Better and faster. CHECKSUM, BINARY_CHEKSUM, CHECKSUM_AGG.
Try:
SELECT CHECKSUMM_AGG(CHECKSUM(*)) FROM SERVER1.DB1.dbo.Table1
SELECT CHECKSUMM_AGG(CHECKSUM(*)) FROM SERVER2.DB2.dbo.Table2
Do not works with text, ntext, image and sql_variant. (But I think if you do not want compare GB-fields convert(varchar(8000),..) is sufficient in most cases)
Read BOL, experiment, you will find what you need.
Good luck, Sai!
-
Here's another approach. Take a lesson from the VFP world if the total record lenght is not too great.
Create view server1.dbo.View1 as Select key1, key+col1+col2+col3+col4 as compare1 from server1.dbo.table1
go
Create view server2.dbo.View 2 as Select key2, key+col1+col2+col3+col4 as compare2 from server2.dbo.table2
go
Create procedure testfordifferences as Begin
Create table #test (source as char(20), key as whatever)
Insert into #test (source, key) Select "Server1", key from View1 where compare1 not in (select compare 2 from server2.dbo.View 2)
Insert into #test (source, key) Select "Server2", key from View2 where compare2 not in (select compare 1 from server1.dbo.View 1)
Select * from #test
End
go
exec testfordifferences
If nothing lists they are the same. Otherwise you will get a list of keys where diffeneces exist. You will have to add code in the views to handle columns with nulls if there are any. This also assumes that you already have the two servers talking to each other.
...JS
-
If server2 is a linked server on server1 you just do
SELECT *
from database1.dbo.table1 t1
LEFT JOIN server2.database2.dbo.table2 t2 ON t1.keyfield = t2.keyfield
WHERE t1.col2 <> t2.column2
UNION
SELECT *
from database1.dbo.table1 t1
RIGHT JOIN server2.database2.dbo.table2 t2 ON t1.keyfield = t2.keyfield
WHERE t1.col2 <> t2.column2
This should show all the rows that are different.
If server2 is not a linked server on server1 then replace "server2.database2.dbo.table t2" in the above SQL with "OPENROWSET('SQLOLEDB','server2';'Server2UserName';'Server2Password', 'select * from database2.dbo.table2') AS t2"
Regards
Peter
-
Try set operations like the following if the column positions & type are same in both the tables even if the column names are different.
select * from table1 minus select * from table2
select * from table2 minus select * from table1
Bth should return 0 rows, then the rows are same else the rows which are different will be returned in both the queries. This Works in Oracle , not sure about SQl server.
-
Well that's easy - first you got to make sure that you have linked server
so that you can access the table on the other server - I presume that the both the tables
have same data with same number or rows in it.
do the following :
select count(*) from <table A>
Gives the row count (X rows)
select count(*) from server..<table B>
Get the row count (X rows)
then do the following:
select * from <table A>
join server..<table B> on
<table A>.Column1 = <table B>.Column1
... do this join for all the columns
if the resulting number of rows is the same then u know
that the two tables are matching.
-
I like the CHECKSUM processing for simply identifying rows that have changed with good performance.
Another solution I have used in the past is to Bulk-copy out the two tables using the QUERYOUT option to order the rows and then use a batch compare utility (like PVCS or TEXTPAD) to compare the files and get a easily readable report of differences. Works good if the files are not too big.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply