CODEWITHSUNDEEP
sqlsql-server
Philip
Philip

Reputation: 2628

count between two tables and finding difference

I currently have the following code which works. It's comparing two tables that are exactly the same but in two separate databases to ensure they have the same record count.

I was wondering if anyone saw a better way of achieving the below?

Declare @count1 int
Declare @count2 int

select @count1 = count(*) from database1.dbo.table1

select @count2 = count(*) from database2.dbo.table1

if @count1 <> @count2
begin
   insert into log table saying counts don't matc
end

Upvotes: 0

Views: 531

Answers (2)

Szymon
Szymon

Reputation: 43023

There's really no much better way. You can just do it without variables:

if (select count(*) from database1.dbo.table1) <> (select count(*) from database2.dbo.table1)
begin
   insert into log table saying counts don't matc
end

Upvotes: 1

Wietze314
Wietze314

Reputation: 6020

If you want to know where the differences are you can use this to find the missing records in database 2

SELECT *
FROM database1.dbo.table1 D1
LEFT JOIN database2.dbo.table2 D2
ON D1.id = D2.id
WHERE D2.id IS NULL

Upvotes: 0

Related Questions