Version 2.0 of my Scripts to Compare RM7 databases to RM9 databases

#1 · 2024-02-23, 11:37 am

Quote from thejerrybryan on 2024-02-23, 11:37 am
I thought maybe I had posted this already, but I can't find it. If this is a second post, my apologies.
I posted Version 1.0 of the scripts already. Those scripts depended on all the primary keys matching for all the tables between RM7 and RM9. They were expected to be run immediately after importing from RM7 to RM9 or after using Tom's script to revert from RM9 to RM7. Those process are table by table, row by row copies that can retain all the primary keys.
But I wanted to be able to enter new data into both RM7 and RM9 and to be able to compare the results. That means not depending on primary keys to match.
The "attach" script attaches your RM7 and RM9 database. It will need to be edited to reflect your file names.
The _compall script compares all tables and identifies which tables have differences. All the other scripts compare one table. So you run _compall first and then run table by table comparisons to identify specific differences. Usually I have differences in no tables at all or maybe in only one or two tables.
The _compall script is created by merging all the scripts for the individual tables and then stripping out the strings of the form --qq and replacing them with nothing. The -- makes the statements into comments and the --qq makes the strings to be removed uniquely identifiable. Removing the --qq strings activates the statements in the _compall script that are commented out inthe scripts for the individual tables. You don't have to do any of that if you want to just run the scripts without any changes. But if you do want to make any changes, you will want any changes you make for any particular tables to be included in the _compall script.
I merge the scripts into _compall by doing something like the following on the Windows command line.
COPY comp* _compall
Then I edit the resultant _compall.sql to remove the --qq strings. Doing it with the Windows COPY command, there will be a SUB character towards the end of the script that has to be removed while you are editing out the --qq strings. Notepad works just fine this simple editing. I'm sure that alternatives to the Windows Copy command exist that don't introduce the obnoxious SUB character just the one time at the end of the file.

I thought maybe I had posted this already, but I can't find it. If this is a second post, my apologies.

I posted Version 1.0 of the scripts already. Those scripts depended on all the primary keys matching for all the tables between RM7 and RM9. They were expected to be run immediately after importing from RM7 to RM9 or after using Tom's script to revert from RM9 to RM7. Those process are table by table, row by row copies that can retain all the primary keys.

But I wanted to be able to enter new data into both RM7 and RM9 and to be able to compare the results. That means not depending on primary keys to match.

The "attach" script attaches your RM7 and RM9 database. It will need to be edited to reflect your file names.

The _compall script compares all tables and identifies which tables have differences. All the other scripts compare one table. So you run _compall first and then run table by table comparisons to identify specific differences. Usually I have differences in no tables at all or maybe in only one or two tables.

The _compall script is created by merging all the scripts for the individual tables and then stripping out the strings of the form --qq and replacing them with nothing. The -- makes the statements into comments and the --qq makes the strings to be removed uniquely identifiable. Removing the --qq strings activates the statements in the _compall script that are commented out inthe scripts for the individual tables. You don't have to do any of that if you want to just run the scripts without any changes. But if you do want to make any changes, you will want any changes you make for any particular tables to be included in the _compall script.

I merge the scripts into _compall by doing something like the following on the Windows command line.

COPY comp* _compall

Then I edit the resultant _compall.sql to remove the --qq strings. Doing it with the Windows COPY command, there will be a SUB character towards the end of the script that has to be removed while you are editing out the --qq strings. Notepad works just fine this simple editing. I'm sure that alternatives to the Windows Copy command exist that don't introduce the obnoxious SUB character just the one time at the end of the file.

Uploaded files:

comp_rm7_rm9_folder_new.zip