Unanswered: Need help with row count validation query.
I am trying to write a query that will count and compare the number of rows of two tables on two different databases. If they are equal then a record will be inserted into another table as 'Pass', else it will result in a 'Fail'.
I can get the portion that enters the pass/fail to work.
But I would also like to insert data to a column called validation_type. What would I have to add to the following query to insert into another specified column?
(SELECT CASE WHEN (SELECT COUNT(1) FROM db1.transaction)
= (SELECT COUNT(1) FROM db2.transaction)
ELSE 'fail' END);
insert into db3.validation(validation_type, test_result)
(select 'count_validation', (case when db2.cnt1 = db1.cnt2 then 'pass' else 'fail' end)
from (select count(1) as cnt1 from lead.transaction) db2 cross join
(select count(1) as cnt2 from stage.transaction) db1);