How to limit a SQL Data Comparison based on the first character of the table name?
npensa
Posts: 5 New member
I'm creating a script to migrate the changes from one database to another one, but the database is very large. I'd like to limit the script based on the first character of the table name (Tables beginning with non-alphanumeric characters, tables that start with the letter 'a', etc.) to I can run the process as a series of smaller scripts instead of one large one. I saw in the documentation how you can limit it to include table names that contain a certain phrase, but I'm not looking to filter it by words that contain the letter 'a' for example, but by those that start with that character. The script is a Powershell script, so I would need to accomplish this with command line. How would I do this?
Tagged:
Answers
I'm not sure which thing you are referring to regarding "include table names that contain a certain phrase" but with the /Include switch on the SQL Data Compare command line the third part in the /Include option is a regular expression, so it should be able to do a "starts with" option using the carrot ^ operator.
I believe the switch would look like this to include tables that start with "a":
This, however, won't affect the initial load of the comparison as at that point all of the tables are still being compared I'm afraid. Meaning, it will not be filtered at that point and the tables will all, always be compared because there is no "prefilter" (or ignore options as they are called in the Oracle tools) in the SQL Server tools. The option above for including (or excluding with /Exclude) are related to what will be deployed.
I hope that helps!
Kind regards,
Alex
Have you visited our Help Center?
I have some very large tables that are static, and the values in them don't change. We're looking to try and exclude them from even being compared at all so it doesn't drive up the time to migrate changes between these two databases. I saw this article for the WHERE clause that can be used in the UI to filter rows for comparison (https://documentation.red-gate.com/sdc/setting-up-the-comparison/filtering-the-comparison-with-a-where-clause) and this one where you can create a filter for a comparison (https://productsupport.red-gate.com/hc/en-us/articles/360005881437-How-to-Create-a-Filter-for-Command-Line-Comparisons).
1. Do either of these prevent tables that I don't want compared being compared?
2. Is there any way at all to trim down the comparison and update time so it doesn't take as long since that is the main reason that I'm attempting to write this script?
Basically, we don't want to spend 30 minutes comparing a massive table that will never have data changes between the two databases if we don't have to.
I understand the problem, but unfortunately it is currently not possible to do so. There is not currently a way to prevent tables and their data from being processed in the initial comparison.
The Filter option, like the exclude option, is only after the initial comparison has taken place an - it is for what is displayed within the results of the comparison (and therefore what can be deployed).
The WHERE clause could help with speed, but it is for finding specific data to compare within the tables and does not prevent the tables from being compared.
You should vote on this Uservoice request which is related to what you are looking for - a way to prevent tables or views from being included within the comparison: https://redgate.uservoice.com/forums/147879-sql-data-compare/suggestions/3967279-filters-for-the-tables-views-tab which is currently under review.
Kind regards,
Alex
Have you visited our Help Center?