I am working on a ETL testing project, where my need is to compare data between two tables from two different databases. to do this, I first downloaded entire tables using query like below.
query_table_a <- paste0("SELECT * FROM MBR_MEAS (NOLOCK)")
table_a <- as.data.frame(sqlQuery(cn, query_table_a))
mismatch_records <- anti_join(table_a, table_b, by="client_id")
merge(x = table_c, y = table_d, by.x ="CLIENT_ID", by.y = "ClientId", all.x = "TRUE")
mismatch_records <- anti_join(table_c, table_d, by = c("CLIENT_ID" = "ClientId"))