Compare two tables using t-sql.

Question

Post reply

Compare two tables using t-sql.

chinn

SSCrazy

Points: 2840
More actions
April 25, 2007 at 8:55 am

#370619

Hello Everybody,
I want to compare the whole row in two tables and if there are not any column changes ignore and if theere are any changes
i should pick them up.
Could any body help me with a solution or if you can give a kick start also i can pick it up from there to.

Thanks IN ADVANCE!

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Camilo Torres SSCrazy Points: 2034 More actions · Answer 1

You may want to play with the EXCEPT AND INTERSECT

select

* from table_1

select

* from table_2

select

* from table_1

except

select

* from table_2

Antares686 SSC Guru Points: 125444 More actions · Answer 2

If these are on the same server

For SQL 2000 you don't have those options but this can help

SELECT

A.KeyColumn,

B.KeyColumn

FROM

(select KeyColumn, CHECKSUM(*) CheckMe from dbName1.dbo.Table1) A

FULL JOIN

(select KeyColumn, CHECKSUM(*) CheckMe from dbName2.dbo.Table2) B

ON

A.KeyColumn = B.KeyColumn

WHERE

A.KeyColumn IS NULL

OR B.KeyColumn IS NULL

OR A.CheckMe != B.CheckMe

If SQL 7 then you will have to join the two and do a list of the columns like so

SELECT

A.KeyColumn,

B.KeyColumn

FROM

dbName1.dbo.Table1 A

FULL JOIN

dbName2.dbo.Table2 B

ON

A.KeyColumn = B.KeyColumn

WHERE

A.KeyColumn IS NULL

OR B.KeyColumn IS NULL

OR A.Col1 = B.Col1

OR (A.Col1 IS NULL AND B.Col1 IS NOT NULL)

OR (A.Col1 IS NOT NULL AND B.Col1 IS NULL)

OR A.Col2 = B.Col2

OR (A.Col2 IS NULL AND B.Col2 IS NOT NULL)

OR (A.Col2 IS NOT NULL AND B.Col2 IS NULL)

...

Possibly better to buy a tool such as Red Gates DataCompare if you need to do this often and for multiple tables.

chinn SSCrazy Points: 2840 More actions · Answer 3

Thanks For Your Help!

But I have 10 columns in a table can i use the same above T-sql.

please let me know!

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Which version of SQL and what are the tables definition but you can always use option for SQL 7.

chinn SSCrazy Points: 2840 More actions · Answer 5

This is on sql server 2000.

Here is how it is

i have two tables

t1 and t2 both have same columns

t1

uid fname lname dept unit MgrName MgrID

1 rob Tom HR Bus Nancy 23

1 rob Tom Web Public Kathy 26

2 bor Mott HR Bus Thomas 25

t2

1 rob Tom HR Bus Thom 24

1 rob Tom Web Public Kathy 26

2 bor Mott HR Bus Thomas 25

From the above scenario,i should pick up

UID 1(ONLY 1st row) as change because his mgrname changed

and ignore UID2 and second row because nothing changed.

Thanks in Advance!

Camilo Torres SSCrazy Points: 2034 More actions · Answer 6

You are out of luck using the EXCEPT which works beautifully in SQL2005 for your case. However, T-SQL suggested above also works for SQL-2000.

loach Right there with Babe Points: 796 More actions · Answer 7

This just came up here today. We were having some trouble with replication. We wound up using:

http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=596

Though we had to make some changes to it since our DB uses a case-sensitive collation and the case used in its references to system tables was inconsistent.

Anyhow, it worked.

rg.mahesh2006 SSC Journeyman Points: 78 More actions · Answer 8

Hello Everybody,

I want to compare the whole row in two tables and if there are not any column changes ignore and if there are any changes

i should pick them up.

Could any body help me with a solution or if you can give a kick start also i can pick it up from there to.

I started of with the following query which absolutely works fine BUT ONLY FOR LESS NUMBER OF COLUMNS IN TABLES,

IF THERE ARE MORE NUMBER OF COLUMNS THEN THE FOLLOWING APPROACH IS NOT FEASIBLE

SELECT MIN(TableName) as TableName, ID, COL1, ADDDATE

FROM

(

SELECT 'Table A' as TableName, A.ID, A.COL1, A.ADDDATE

FROM A

UNION ALL

SELECT 'Table B' as TableName, B.ID, B.COL1, B.ADDDATE

FROM B

) tmp

GROUP BY ID, COL1,ADDDATE

HAVING COUNT(*) = 1

ORDER BY ID

Thanks IN ADVANCE!

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

Do Antares's solutions not work? If no, tell us why not and we'll see if we can help.

John

rg.mahesh2006 SSC Journeyman Points: 78 More actions · Answer 10

Hello Everybody,

I want to compare the whole row in two tables and if there are not any column changes ignore and if there are any changes

i should pick them up.

Could any body help me with a solution or if you can give a kick start also i can pick it up from there to.

I started of with the following query which absolutely works fine BUT ONLY FOR LESS NUMBER OF COLUMNS IN TABLES,

IF THERE ARE MORE NUMBER OF COLUMNS THEN THE FOLLOWING APPROACH IS NOT FEASIBLE

SELECT MIN(TableName) as TableName, ID, COL1, ADDDATE

FROM

(

SELECT 'Table A' as TableName, A.ID, A.COL1, A.ADDDATE

FROM A

UNION ALL

SELECT 'Table B' as TableName, B.ID, B.COL1, B.ADDDATE

FROM B

) tmp

GROUP BY ID, COL1,ADDDATE

HAVING COUNT(*) = 1

ORDER BY ID

IF ANY ONE HAS ANY OTHER SOLUTION PLEASE HELP ME OUT

Thanks IN ADVANCE!

epran_123 Valued Member Points: 57 More actions · Answer 11

Use CHECKSUM_AGG with BINARY_CHECKSUM to detect changes in a table.

BINARY_CHECKSUM ignores columns of noncomparable data types in its computation. Noncomparable data types are text, ntext, image, and cursor, as well as sql_variant with any of the above types as its base type.

Refer to SQL server books online for usage syntax

Let me know if it helps

DanKennedy Hall of Fame Points: 3736 More actions · Answer 12

Although it was originally intended for use in replication the tablediff utility is specifically designed for the problem you're trying to solve. Easy to use, very quick and generates you the SQL to "correct" the data.

You can find it in C:\Program Files\Microsoft SQL Server\90\COM if you installed to the default location.

Dan
www.firstcs.co.uk