SQL Data Compare mapping rules explained

---

Download xSQL Compare Bundle, starting from $0!

---

By default xSQL Data Compare will automatically "map" tables from the two databases being compared based on the full object name, that is [schemaname].[objectname]. For example table [database1].[dbo].[Customer] will be mapped to table [database2].[dbo].[customer]. For most of the real life scenarios out there this automatic mapping is sufficient however, there are many cases when the tables on both sides cannot be mapped using this approach because:

1. The same tables maybe owned by different schemas on different databases;
2. Table names might not be exactly the same – for example, on one database the customer's table might be named dev_customer whereas on another database might be named prod_customer.

One utility that xSQL Data Compare provides to allow you to map tables regardless of the name is the "compare tables" functionality. This allows you to manually map a table from database1 to any table from database2 regardless of schema name or table name. This is perfect when you only have a few tables that you need to compare, but when you have hundreds or even thousands of tables this manual mapping is not practical. Here is where xSQL Data Compare "mapping rules" come in. There are two name based mapping rules that you can control:

1. Schema name mapping rules. Using the schema mapping rules you can instruct xSQL Data Compare to:
1.  Do an exact match on schema names (default behavior). In this case [database1].[dbo].[customer] will be mapped to [database2].[dbo].[customer] but [database1].[dev].[customer] will not be mapped to [database2].[prod].[customer]
2. Completely ignore the schema name. In this case [database1].[dev].[customer] will be mapped to [database2].[prod].[customer]. 
3. Manually map schema names. You could, for example map the schema “dev” to the schema ‘prod” in which case then xSQL Data Compare will automatically map [database1].[dev].[customer] to [database2].[prod].[customer]
2. Table name mapping rules. Using this rule you can instruct xSQL Data Compare to:
1. Ignore a certain name prefix on the left database and a certain name prefix on the right database when performing the object mapping. For example, you could say ignore the prefix “dev” on the left and the prefix “prod” on the right. In this case, the table [database1].[dbo].[dev_customer] will be mapped to [database2].[dbo].[prod_customer]  
2. Ignore a certain name postfix on the left database and a certain name postfix on the right database.

Download your copy of xSQL Data Compare and see for yourself why this is the best sql data compare tool in the market, and on top of that you might not have to pay anything for it.