June 15, 2009 at 8:16 am
Hi Experts,
I have 2 tables in my database both the structures are same each table contains 170 columns and I would like to compare both tables data
I have server name as primary key and i could see both tables has 600 records.
Please let me know your suggestion , appreciate if you guys send me any script which compares 170 columns in each tables
Thanks
ichbinraj
June 15, 2009 at 8:19 am
You can download red gate sql compare which can do this for you gracefully.
June 15, 2009 at 8:19 am
Use tablediff utility.
http://msdn.microsoft.com/en-us/library/ms162843(SQL.90).aspx
http://www.mssqltips.com/tip.asp?tip=1073%5B/url%5D
June 15, 2009 at 8:23 am
To compare the structure you can use INFORMATION_SCHEMA.COLUMNS:
SELECT
*
FROM INFORMATION_SCHEMA.COLUMNS c1
FULL JOIN INFORMATION_SCHEMA.COLUMNS c2 ON c1.COLUMN_NAME = c2.COLUMN_NAME
WHERE
c1.TABLE_NAME = 'OBJ_Pu'
AND c2.TABLE_NAME = 'OBJ_Pallet'
To compare data you can use EXCEPT to find rows which differ or INTERSECT to find rows which are equal.
Flo
June 15, 2009 at 12:04 pm
This is such a common problem. Rather than trying to roll your own queries every time you need to do this, pick up one of the third party tools that does it. They're inexpensive and you'll use them over and over again. Personally, I recommend Red Gate's SQL Data Compare. I've been using it for something close to 10 years now, I think. It's great. There's also some really good tools for working along these lines from Apex SQL.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 16, 2009 at 4:49 am
"ApexSQL Data Diff 2008" I am using. working fine for me.
June 16, 2009 at 5:10 pm
This is such a common problem, you should roll your own!
Note: my original post contained sucky code. I've repaired it and placed it within a table valued function which makes side by side (or row by row) comparison much easier (see example in comment section.
create function dbo.udf_compare_table_delta(
@table_a sysname
,@table_b sysname
)
/*
select * from (
select * from dbo.udf_compare_table_delta( 'table_a', 'table_b' )
union all
select * from dbo.udf_compare_table_delta( 'table_b', 'table_a' )
) as foo
order by ordinal_position
*/
returns table
as
return
(
select
table_schema
,table_name
,column_name
,ordinal_position
,column_default
,is_nullable
,data_type
,character_maximum_length
,character_octet_length
,numeric_precision
,numeric_precision_radix
,numeric_scale
,datetime_precision
from information_schema.columns as T_A
where table_name = @table_a
and exists (
select *
from information_schema.columns as T_B
where table_name = @table_b
and (
T_A.ordinal_position = T_B.ordinal_position
OR T_A.column_name = T_B.column_name
)
and not (
T_A.ordinal_position = T_B.ordinal_position
and T_A.column_name = T_B.column_name
and T_A.table_schema = T_B.table_schema
and T_A.column_name = T_B.column_name
and ( T_A.column_default is null AND T_B.column_default is null
OR T_A.column_default = T_B.column_default
)
and T_A.is_nullable = T_B.is_nullable
and T_A.data_type = T_B.data_type
and ( T_A.character_maximum_length is null AND T_B.character_maximum_length is null
OR T_A.character_maximum_length = T_B.character_maximum_length
)
and ( T_A.character_octet_length is null AND T_B.character_octet_length is null
OR T_A.character_octet_length = T_B.character_octet_length
)
and ( T_A.numeric_precision is null AND T_B.numeric_precision is null
OR T_A.numeric_precision = T_B.numeric_precision
)
and ( T_A.numeric_precision_radix is null AND T_B.numeric_precision_radix is null
OR T_A.numeric_precision_radix = T_B.numeric_precision_radix
)
and ( T_A.numeric_scale is null AND T_B.numeric_scale is null
OR T_A.numeric_scale = T_B.numeric_scale
)
and ( T_A.datetime_precision is null AND T_B.datetime_precision is null
OR T_A.datetime_precision = T_B.datetime_precision
)
)
)
)
GO
June 17, 2009 at 12:42 am
I agree with Pradeep, use TableDiff.
I've used it myself recently for a very similar problem to the one you describe and it worked fine for me.
June 17, 2009 at 1:16 am
You can write union all then write union query order by the primary key. you can see the difference.
select primarykey as Primarykey,* from T1
union
select primarykey as Primarykey,* from T2
order by Primarykey
if this query return exact 600 rows then both table have same data if it return more than 600 rows then again run
select primarykey as Primarykey,* from T1
union ALL
select primarykey as Primarykey,* from T2
order by Primarykey
put that output into a temp table then run group by (on Primarykey) query you will find the differance.
Thanks
Abhijit
October 6, 2010 at 2:40 am
for table differences in sqlserver 2005 we are using one tool that is in
installed folders C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe
you use this option for table differenceing
[font="Arial"]Sunil Kumar Anna[/font]
October 6, 2010 at 11:25 pm
ps. (6/15/2009)
http://msdn.microsoft.com/en-us/library/ms162843(SQL.90).aspx
http://www.mssqltips.com/tip.asp?tip=1073%5B/url%5D%5B/quote%5D
I second this suggestion. I have used tablediff myself, and while it will need some amount of DOS/batch file techniques, the benefits are:
1. It's FREE! (well, sort of - you pay for it alongwith your SQL license)
2. At least I feel much more "closer" to the SQL engine
3. All the 3rd party utilities would ultimately be using/extending these features which come out-of-the box. So, why should we not use them directly?
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply