Mapping and Filters (Data)

Within Data Compare the mapping feature has less relevance since source tables can be mapped to completely different target tables bypassing the need for mapping. However the ability to filter schema and apply load filters can be useful especially in the case of columns. For example a Date Modified column may exist through the table schema and the compare of this data unwanted. Using the Load Filter and excluding the Date Modified column the Date Modified column will not be visible across the entire data compare project.

Schema Filters restrict the loading objects to specific schema. When active only objects belonging to the schema in the source/target list will be loaded. These can be mapped or equal schema for example adding source PERSON and target PERSON and checking Apply Filter Schema ensures only objects belonging to PERSON in both source and target databases are loaded for compare and scripting.

Object Load Filters will exclude entire object classes, specific objects based on name or part of a name or columns based on name or part name. 

Mapping and Filters can be separately active.

Check Schema Filters and Mapping to activate the mapping function. Automatically the schema of the two databases will be loaded ready for mapping. You must have configured the server and database details prior to switching on schema mapping.

By default DBO and DBO are mapped. In the example FOO will be displayed in the right target column and click to assigned the mapping for DBO to FOO.

To begin mapping click a schema from the source list and this will move the schema name from source to the map list and if Auto Add Target is checked automatically locate the same schema in the target and add that schema. To alter to target just choose a different schema from the target list and click. 

If you wish to use the keyboard to move through the schema list hold Shift down and click the schema list. Then use the up/down arrows to locate and space bar to add the schema.

To delete a map either click the X icon or click the schema map list and press the space bar.

Mapping Features

Apply Filter Schema 
When checked only the selected schema will be loaded and compared. For example you may wish to only load and compare DBO so leave the automatic mapping of DBO to DBO and check Apply Filter Schema. Now SQL Delta will only load objects that belong to DBO. If an object, perhaps a function, relies on an object outside of DBO then any scripting will of course fail or is likely to fail.

Match All 

This is mostly useful for Schema Filtering where you may wish to compare most schema so its quicker to match all and then remove the schema you do not wish to load.

Add <New> Row

Adds a row ready to for adding the source and target schema. Clicking a source schema will automatically add the schema to the map list.

Remove All

This removes all current mapping.

Auto Add Target

This is also useful for schema filtering and when checked will automatically link the source and target. In the above example clicking source GUEST will add Guest to the map list from both source and target. When Auto Add Target is unchecked you must manually select the target for each newly added map item.

Clear Target

This will remove the target item from the selected map. Alternatively click on a different target to replace the existing mapped item.

SCHEMA LOAD FILTERS

Filters can exclude specific objects from the schema load process. To activate load filters check the Load Filter Active.

There are two filter options, filtering objects from the main load and this can be filtering any primary object such as table, view, procedure and so on. The second filter can remove columns from the table load.

Because filters remove the object from load you may receive errors during the load process. For example a foreign key in one table may reference a filtered table. This will of course result in an script error if the foreign key is to be added or changed on the target.

Object Filters

Within Object filters there are two distinct types of filter. Exclude an entire class of object such as exclude all Tables and this uses the form {Table}. The second filter type is to exclude all objects that contain some text.

The text filters can be most useful where an object set has an easily defined subgroup, it could be a set of views within the entire list of views that has been third party generated and you don't wish to modify the view details. Often in these cases the view has a common name and then a unique view name. By adding the common name plus % to the filter those views will be excluded from the load process. One thing to note is SQL Delta will exclude any object matching the filter whether that object is a view or table, procedure etc. It isn't possible to only restrict the filter to views.

The drop down box provides a list of available object class filters or you can just type the text however the text must match the filter name exactly. Then click add to add the new filter. 

For name text filters just type the name you wish to exclude and since SQL Delta uses a like in the query you can use % for part name matching and click add.

Column Filters

Add text that matches the column you wish to remove from the load process. Again this filter uses a like clause so % can be used for matching part of the column name. 

Type your text and click Add to create a list of column names to exclude from the table load.