I have a very large database with millions of rows per table (highest one is 16 million). I also have ~400-500 tables in each DB. I am wanting to compare db1 table test(table name) to db2 table test(table name). The tables do not contain primary keys so I want to search any differences with any of the fields in the tables. I then want redgate to create a script that I can run on db2 so that I can update it to be the same as db1. Can I do this via the CLI?
When I compare the tables in SQL Data Compare for some reason it comes back and says the tables are identical. But I know for fact that they are not (manually querying the db I know that there is a difference in 11 of the rows in db1). There are about 75000 rows in the particular table that I am wanting to compare.
When starting the compare process SQL Data Compare will first get a list of all the tables (which takes ~10 min) and then I can compare the particular tables and it will come back with 0 rows are not identical.
What is the easiest and quickest way to compare all data in the two tables and have this create a script that I can then promote through my environments?
I am using the 14 day evaluation to test this tool out and see if my company should buy it or not. Please let me know if you can assist me with this process. Thank you!