How to narrow the data sub-set for object scripting

Applies to
ApexSQL Script

Summary
This article presents several techniques on how to narrow down the data in the scripting process.

Description
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:

1. The Object filter – select only specific tables
2. The Column filter – select only specific columns
3. The Max value filter – select the maximum row value
4. The Where clause filter – select only specific rows

Object filter

When a database is selected in the New project window, click the Advanced button and click the Object filter tab that will be shown in opened menu below:

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.

The list of all tables in a database will be shown in the right-side pane.

Next, 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 clicking on Filter button and then entering a specific letter or word in the Filter string filed:

Column filter

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 Employee 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:

Max value

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 Employee table has 290 rows and if the Max value is set to 8, only the first 8 rows will be included in the generated script:

Where clause

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 Apply button. For example, if the following condition is specified:

AddressID > 5000 AND AddressID < 12000

and the Apply button is clicked:

The row count number is changed from 15561 to 608. 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: 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 an information if something is wrong with the specified condition in the Where clause window?

A: Yes. First, a short warning message Incorrect syntax 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.