Solved

# compare 2 large tables

Posted on 2013-05-21
353 Views
i have 2 tables, lets say:

table1(id bigint) and table2(id bigint)

table1          table2
1                    3
2                    76
23                  89
45                  90
76                  92
90                  98

so i want to select all rows on table1 that does not exist on table2, i have this in place:

SELECT * FROM TABLE1 AS T1
WHERE (CASE WHEN EXISTS(SELECT TOP 1 ID FROM TABLE2 WHERE ID = T1.ID) THEN 1 ELSE 0 END) = 0

this query takes a lot from the processor and too hard on my server, both of those tables have about 100000 rows. So i need another better solution to this.
0
Question by:rafaelrgl

LVL 10

Expert Comment

1. Make sure you have indices on ID for both tables
2. Try
SELECT * FROM TABLE1 AS T1
WHERE not EXISTS(SELECT TOP 1 ID FROM TABLE2 t2 WHERE t2.ID = T1.ID);
SELECT * FROM TABLE2 AS t2
WHERE not EXISTS(SELECT TOP 1 ID FROM TABLE1 t1 WHERE t1.ID = t2.ID);
0

LVL 9

Accepted Solution

mimran18 earned 500 total points
CREATE PROCEDURE CompareTables(@table1 varchar(100),
@table2 Varchar(100), @T1ColumnList varchar(1000),
@T2ColumnList varchar(1000) = '')
AS

declare @SQL varchar(8000);

IF @t2ColumnList = '' SET @T2ColumnList = @T1ColumnList

set @SQL = 'SELECT ''' + @table1 + ''' AS TableName, ' + @t1ColumnList +
' FROM ' + @Table1 + ' UNION ALL SELECT ''' + @table2 + ''' As TableName, ' +
@t2ColumnList + ' FROM ' + @Table2

set @SQL = 'SELECT Max(TableName) as TableName, ' + @t1ColumnList +
' FROM (' + @SQL + ') A GROUP BY ' + @t1ColumnList +
' HAVING COUNT(*) = 1'

exec ( @SQL)

http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx
0

LVL 48

Expert Comment

``````select
*
from table1
inner join (
select ID
from table1

except

select ID
from table2
) cept on table1.id = cept.id
``````
0

LVL 10

Expert Comment

If you want to compare data of two tables then you may use Visual Studio's Data/Schema comparison options or even SqlDelta.
0

LVL 12

Expert Comment

as per your given example above,

i've tried to create a small example of except command which can get you near to the results.

DECLARE @Temp1 table (col1 int)
insert into @Temp1(col1)values(1)
insert into @Temp1(col1)values(2)
insert into @Temp1(col1)values(3)
insert into @Temp1(col1)values(4)
insert into @Temp1(col1)values(5)
insert into @Temp1(col1)values(6)

DECLARE @Temp2 table (col1 int)
insert into @Temp2(col1)values(1)
insert into @Temp2(col1)values(4)
insert into @Temp2(col1)values(6)
insert into @Temp2(col1)values(7)
insert into @Temp2(col1)values(4)
insert into @Temp2(col1)values(8)

select * from @Temp1
except
select * from @Temp2

please give  a try & hope this helps.
0

LVL 47

Expert Comment

SELECT T1.*
FROM Table1 as T1
LEFT JOIN Table2 as T2
ON T1.ID = T2.ID
WHERE T2.ID IS NULL
0

LVL 1

Author Closing Comment

this way is really faster compare to the one i was using.
0

## Featured Post

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.