This article presents several techniques on how to narrow down the data in the scripting process.
When there is a need to create a script that contains data for updating specific rows of tables in a SQL database, ApexSQL Script can provide several options. These options can narrow down data to a specific row, reduce the time and the size of the created SQL script, and allow to execute it only on desired rows.
There are 4 ways to narrow it down:
- The Object filter – select only specific tables
- The Column filter – select only specific columns
- The Max value filter – select the maximum row value
- The Where clause filter – select only specific rows
When a database is selected in the New project window, click the Advanced button in the bottom-left corner and click the Object filter tab that will be shown on the left side of the window:
When the Object filter tab is shown, all object types will be shown and all of them are checked by default. Since only tables need to be selected for the scripting process, all other objects can be unchecked. To further filter tables to specific ones, check the Filter check-box next to the tables and then select Tables node, under the Common object type, in the left pane:
The list of all tables in a database will be shown in the right-side pane. The first option to filter out tables is the Pattern filter that uses regular expression strings. For example, if there is a need to include all tables that start with the Product word, regular expression string, in that case will be ^Product and once that pattern is checked it will be applied on the list:
Learn more about using regular expressions in ApexSQL tools from here.
Furthermore, the list of tables can be filtered by checked/unchecked tables. If some tables where unchecked in the scripting process and those one doesn’t need to be shown in the list, click the Filter field above the column of checkboxes and select the check-marked box:
The list of tables can be also filtered by Name and/or Schema. Filtering by Name and/or Schema can be done by entering a specific letter or word in the Name/Shema filed, or by selecting a specific one from the drop-down list:
Once the specific objects are selected and a database is loaded, click the Data button from the Home tab in the View section:
In the Data view, all previously selected tabled in the Object filter will be shown, along with their columns. By default, all tables are unchecked. If one table is checked, in this example the Person table, all columns for that table will be automatically checked in the Fields pane below. But, if there is no need to include all of them, just uncheck the not needed ones and reduce the amount of data on column level:
The Max value filter can be set for each table in the Data view and it defines a new row value, i.e. a new maximum value of scripted rows will be set. The Max value filter can be defined by entering a value in the Max field for the selected table or by right-clicking the same field and choosing the Edit max value option from the right-click context menu:
For example, the Person table has 19972 rows and if the Max value is set to 8, only the first 8 rows will be included in the generated script:
The last filter that can narrow down the scripting process to specific rows is the Where clause filter. Similar to the Max value filter, the Where clause filter can be set for each table and it can be initiated with a click on the “ellipse” button in the Where field or by selecting the Edit SQL Where clause from the right-click context menu:
In both ways, the Where clause window will be opened for the selected table:
The current row count is shown in the bottom-left window. Any specified condition will affect the row count after clicking the Refresh button. For example, if the following condition is specified:
BusinessEntityID > 13487 AND BusinessEntityID < 17394
and the Refresh button is clicked:
The row count number is changed from 19972 to 3906. In this way, the scripting process will be limited to a specified condition and only the rows that meet this condition will be included in the generated script.
Once the condition is set, click the OK button to close the Where clause window, and apply the filter for the selected table:
The checkmark for the Where field implies that a Where filter condition is set for the selected table.
Q: Is there any specific list of regular expressions that can be used for the Pattern filter?
A: No. You can use all available regular expressions and you can check the complete list on this link.
Q: If I select only a couple of columns for the scripting process, will the rest of columns be deleted upon the script execution?
A: No. The generated script will only update data for the checked columns, the other columns won’t be affected in any way.
Q: Which syntax can be used in the Where clause window?
A: Any syntax that can be used for the WHERE clause, can be used in the Where clause window to narrow down the set of rows.
Q: Is there some kind of auto-complete that can help me while typing the WHERE clause?
A: Yes. As soon as you start typing the condition, the auto-complete drop-down list will appear, providing the list to automatically fill in SQL keywords and names:
Q: Is there an information if something is wrong with the specified condition in the Where clause window?
A: Yes. First, a short warning message will be shown below the Where clause field:
Second, if you click the OK button, a full warning message will be shown:
Once the OK button is clicked in this warning window, you’ll be brought back to the Where clause window to resolve the encountered issues before proceeding any further.