# Comparing two tables

• ### Question

• Tools: SQL server 2008

Environment: Windows Vista

Problem: I would like to compare table A with Table B. If table A records dont match with B then list those differences

Giving info:

Table A

012345655
012345667
012345656
012345673
012345675
012345670
012345679
012345674
012345678
005555599
005555599
004444499
004444499
003333399
003333399
002222299
002222299
001111199
001111199
005555599
004444499
003333399
002222299
001111199
009555599
009444499
009333399
009222299
009111199
099555599
099444499
099333399
099222299
099111199

Table B

012345655
012345667
012345656
012345673
012345675
012345670
012345679
012345674
012345678
005555599
003333399
001111199
004444499
002222299
009555599
009444499
009333399
009222299
009111199
099555599
099444499
099333399
099222299
099111199

both tables have one column and it is ID

Table A has 34 records

Table B has 24 records

I am curious to know how to list/display the 10 records

```create table Dummy ( id  int )
insert into dummy (id) values (012345655)
insert into dummy (id) values (012345667)
insert into dummy (id) values (012345656)
insert into dummy (id) values (012345673)
insert into dummy (id) values (012345675)
insert into dummy (id) values (012345670)
insert into dummy (id) values (012345679)
insert into dummy (id) values (012345674)
insert into dummy (id) values (012345678)
insert into dummy (id) values (005555599)
insert into dummy (id) values (005555599)
insert into dummy (id) values (004444499)
insert into dummy (id) values (004444499)
insert into dummy (id) values (003333399)
insert into dummy (id) values (003333399)
insert into dummy (id) values (002222299)
insert into dummy (id) values (002222299)
insert into dummy (id) values (001111199)
insert into dummy (id) values (001111199)
insert into dummy (id) values (005555599)
insert into dummy (id) values (004444499)
insert into dummy (id) values (003333399)
insert into dummy (id) values (002222299)
insert into dummy (id) values (001111199)
insert into dummy (id) values (009555599)
insert into dummy (id) values (009444499)
insert into dummy (id) values (009333399)
insert into dummy (id) values (009222299)
insert into dummy (id) values (009111199)
insert into dummy (id) values (099555599)
insert into dummy (id) values (099444499)
insert into dummy (id) values (099333399)
insert into dummy (id) values (099222299)
insert into dummy (id) values (099111199)
select * from Dummy -- 34 rows

;WITH CTE (id,rn)
AS
(
SELECT id,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) AS rn
FROM Dummy
)

select * from Dummy where id in (DELETE FROM CTE WHERE rn > 1)

drop Table Dummy```

• Edited by Monday, July 30, 2012 4:36 PM
Friday, July 27, 2012 10:26 PM

• SELECT *

INTO C

FROM A
EXCEPT
SELECT * FROM B

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

• Marked as answer by Monday, July 30, 2012 8:32 PM
Monday, July 30, 2012 8:12 PM
• ```;With CTE1
As
(
Select	ID, Row_Number() Over(Partition By ID Order by ID) As RN
From	Table_A
)
,CTE2
As
(
Select	ID, Row_Number() Over(Partition By ID Order by ID) As RN
From	Table_B
)

Select	ID
From
(
Select	ID, RN
From	CTE1
Except
Select	ID, RN
From	CTE2
) X```

| Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

Hate to mislead others, if I'm wrong slap me. Thanks!

• Marked as answer by Monday, July 30, 2012 8:32 PM
Saturday, July 28, 2012 7:54 AM

### All replies

• SELECT * FROM A
EXCEPT
SELECT * FROM B

If you want to list rows in A that are not in B.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
• Proposed as answer by Saturday, July 28, 2012 5:12 AM
Friday, July 27, 2012 10:36 PM
• Could you try this script? It will show you unique rows from each table.

```DECLARE @SQL varchar(8000)
DECLARE @Table1 varchar(8000)
DECLARE @Table2 varchar(8000)
SET @Table1 = 'TableA'
SET @Table2 = 'TableB'

DECLARE @Cols varchar(8000)
SET @Cols = ''
SELECT @Cols = @Cols + '[' + Column_Name + '],'
FROM INFORMATION_SCHEMA.Columns
WHERE Table_Name = @Table1 and DATA_TYPE <> 'xml'
SET @Cols=left(@cols,LEN(@cols)-1) -- Remove trailing ,

SET @SQL = +
'SELECT Max(TableName) as TableName, ' + @Cols +
'  FROM ( ' +
'    SELECT ''' + @Table1 + ''' AS TableName, ' + @Cols +
'      FROM ' + @Table1 +
'     UNION ALL ' +
'    SELECT ''' + @Table2 + ''' As TableName, ' + @Cols +
'      FROM ' + @Table2 +
'    ) A ' +
' GROUP BY ' + @Cols +
' HAVING COUNT(*) = 1 ' +
' ORDER BY ' + @Cols

-- SELECT @SQL
EXEC ( @SQL)

```

Thanks!
Aalam | Blog (http://aalamrangi.wordpress.com)

Saturday, July 28, 2012 1:38 AM
• ```;With CTE1
As
(
Select	ID, Row_Number() Over(Partition By ID Order by ID) As RN
From	Table_A
)
,CTE2
As
(
Select	ID, Row_Number() Over(Partition By ID Order by ID) As RN
From	Table_B
)

Select	ID
From
(
Select	ID, RN
From	CTE1
Except
Select	ID, RN
From	CTE2
) X```

| Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

Hate to mislead others, if I'm wrong slap me. Thanks!

• Marked as answer by Monday, July 30, 2012 8:32 PM
Saturday, July 28, 2012 7:54 AM
• SELECT * FROM A
EXCEPT
SELECT * FROM B

If you want to list rows in A that are not in B.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

how to store the results in a table C

A except B = C

I am curious to know how

Monday, July 30, 2012 3:37 PM
• ```;With CTE1
As
(
Select	ID, Row_Number() Over(Partition By ID Order by ID) As RN
From	Table_A
)
,CTE2
As
(
Select	ID, Row_Number() Over(Partition By ID Order by ID) As RN
From	Table_B
)

Select	ID
From
(
Select	ID, RN
From	CTE1
Except
Select	ID, RN
From	CTE2
) X```

This is an excellent approach. I like the logic behind it. However, it did not work

(34 row(s) affected)

(10 row(s) affected)

Msg 208, Level 16, State 1, Line 2
Invalid object name 'CTE1'.

Table A has 34 records. These records are stored in ##dummy

In Table A or ##dummy, there are 10 duplicate records and 24 records are unique

```GO

;WITH CTE1

AS

( SELECT  ID, row_number() OVER(PARTITION BY ID ORDER BY ID) AS RM FROM ##Dummy )

Delete from CTE1 WHERE RM > 1 --<< remove dups

GO

;with CTE2
As
( Select ID, Row_Number() Over(Partition By ID Order by ID) As RN From ##Dummy )

Select	ID
From
(
Select	ID
From CTE1
Except
Select	ID
From	CTE2
) X

DROP Table ##Dummy```

• Edited by Monday, July 30, 2012 3:56 PM
Monday, July 30, 2012 3:55 PM
• Hi Sandra, the CTE is not working the way you were trying to do.

your referened CTEs can't break by Go or any other DML statement.

YOu CTEs can only be followed by its immediate DML statements.

Your modified query is with wrong syntax.

| Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

Hate to mislead others, if I'm wrong slap me. Thanks!

Monday, July 30, 2012 7:55 PM
• what do you suggest to fix the modified query. I am curious to learn. please advised

Monday, July 30, 2012 8:11 PM
• SELECT *

INTO C

FROM A
EXCEPT
SELECT * FROM B

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

• Marked as answer by Monday, July 30, 2012 8:32 PM
Monday, July 30, 2012 8:12 PM
• There is more details available on intersect and except in MSDN link: http://msdn.microsoft.com/en-us/library/ms188055.aspx

You may find it useful to know more about these types to reduce the number of steps in getting the result as well as performance of the queries.

Akkiraju Ivaturi

Tuesday, July 31, 2012 5:09 AM
• Thanks Akkiraju!!! for sharing the info
Tuesday, July 31, 2012 3:12 PM