count matched in 2 table

Question

count matched in 2 table

Shambhu Rai 1,411

Hi Expert ,

i wanted to match the count of 2 table if it is not match then it should replied 'not matched'

Create table table1_new(col varchar (20),col1 varchar (20))

Create table table2_new(col varchar(20),col1 varchar (20))

Insert into table2_new values (10,60)

Insert into table2_new values (20) Insert into table2_new values ('test','test3')

Insert into table2_new values ('test1')

select Count(*) from table1_new

select Count(*) from table2_new

expected output: 1 or ' not matched' like in if condition

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Erland Sommarskog 129.1K MVP Volunteer Moderator

Had you tested your script before you posted, you would have found that it had errors.

It would also have helped if you had given a background to what you want to achieve. But from the terse description you have given, this meets your requirement:

IF EXISTS (SELECT * FROM table1_new
           EXCEPT
           SELECT * FROM table2_new)
   PRINT 'Not matched'
ELSE 
   PRINT 'Matched'

By the way, I deleted two tags from your question that were not relevant to the question.

Shambhu Rai 1,411 Reputation points

2023-10-14T11:36:51.1466667+00:00

sorry for that...can you tell me which row is not matching exactly using above query
Erland Sommarskog 129.1K Reputation points MVP Volunteer Moderator

2023-10-14T11:43:39.6366667+00:00
That question was not in the spec. As I said it would have helped if you had given a background to what you want to achieve.

But if you run

SELECT * FROM table1_new EXCEPT SELECT * FROM table2_new SELECT * FROM table2_new EXCEPT SELECT * FROM table1_new

You will see the mismatching rows.

Shambhu Rai 1,411

Sir,

i check the query , count is showing incorrect values

  truncate table table1_new
truncate table table2_new
Insert into table1_new values (10,60) 
Insert into table2_new values (10,60) 
Insert into table2_new values (10,70)

IF EXISTS (SELECT count(*) FROM table1_new
           EXCEPT
           SELECT count(*) FROM table2_new)
   PRINT 'Not matched'
ELSE 
   PRINT 'Matched'

Output: it should give mismatch output for count

User's image

Erland Sommarskog 129.1K Reputation points MVP Volunteer Moderator

2023-10-14T12:31:41.2833333+00:00

Dunno. When I run your query as posted I get Not matched. I can't say what is going on in your case.
Shambhu Rai 1,411 Reputation points

2023-10-14T12:39:44.06+00:00

Can you share the screenshot
Erland Sommarskog 129.1K Reputation points MVP Volunteer Moderator

2023-10-14T12:43:37.0266667+00:00
Shambhu Rai 1,411 Reputation points

2023-10-14T12:45:58.04+00:00

When tried this it works but this solution will not be accepted by others. here it is showing which row i mismatch as it is lengthy
Erland Sommarskog 129.1K Reputation points MVP Volunteer Moderator

2023-10-14T12:48:59.22+00:00

Yeah, but without more details on the requirements, we can't help you further.
Shambhu Rai 1,411 Reputation points

2023-10-14T12:52:20.95+00:00

Sir, the requirement is if the count is match then it should say 'count matched' and when unmatched then it should show row like above results that sit

please run below statement

it is showing wrong output

Erland Sommarskog 129.1K MVP Volunteer Moderator

You mean like this?

IF (SELECT COUNT(*) FROM table1_new) = 
   (SELECT count(*) FROM table2_new)
   PRINT 'Count matches'
ELSE 
BEGIN
   (SELECT * FROM table1_new
   EXCEPT
   SELECT * FROM table2_new)
   UNION
   (SELECT * FROM table2_new
   EXCEPT
   SELECT * FROM table1_new)
END

Shambhu Rai 1,411 Reputation points

2023-10-14T13:07:07.3466667+00:00

My Apology, the query is working ... strange :).. how can i get the row only when the data is not matched
Shambhu Rai 1,411 Reputation points

2023-10-14T15:44:35.2+00:00
@Erland Sommarskog from new thread response as duplicate...on every new question... will get only one answer thats a rule... for anything new we raise another thread BTW see below response

we can not use this complex queries when the data is huge else statement will affect the performance badly.. and there will be question on below query. after BEGIN... (SELECT * FROM table1_new EXCEPT SELECT * FROM table2_new) UNION..........
Erland Sommarskog 129.1K Reputation points MVP Volunteer Moderator

2023-10-14T16:45:52.3033333+00:00

You never said anything about the volumes until now.

To get good answers to your questions, you need to ask good question. If you only ask about a tiny detail, you will only get an answer about that tiny detail. We can read your thoughts or guess your actual requirements.

If you ask a question where you start with telling that you have two tables because of <fill in the blanks>, you tell why want to compare them, what volumes you have, and you also provide proper table definitions with keys, I promise I will not mark that question as a duplicate.

But as long as you are asking sloppy questions, you will get sloppy answers.

Share via

count matched in 2 table

0 additional answers

Your answer