# SQL Matching

SQL Matching is the inverse of Standard Matching. Instead of building an in memory index using expressions, you build a SQL SELECT statement. This SQL statement is then used against the target table for each incoming record, to find the possible matches.

### Advantages

Because there is no in memory index being built, there is no starting time used to create the indexes and no memory used to maintain them.

The only requirement on the SQL is that it return a valid value for the primary key of the target table. This means that the SQL can query a different table, or multiple tables.

If your source data happens to have a field with the correct primary key value for the target table, you do not need the SQL; you can use just the name of the field. For example, if your source data has the accountid field, you can use teh simple statement:

#accountid

### Disadvantages

Creating a SQL match requires you to complete a SQL SELECT statement. Inaport will provide the base of the statement to provide assistance, but you must complete it.

At run time,a SQL SELECT is executed against the target table for each incoming record. This can have performance implications if the query cost is high (for example, when used for Sage CRM.com).

### When to use

SQL matching is the best technique when the time cost of building the in memory indexes for standard matching is too high. It is particularly effective when you have a relatively small number of source records going into a large target table.

### Selecting the Connector

Inaport allows you to use the TARGET connector (default) or the SOURCE connector to execute the SQL against. This means that if your source data source is faster than the target (for example, the source is SQL Server and the target is Microsoft CRM) and the data has cached the primary keys of the target, you can query the source instead of the target.

### Details

To create a SQL Match, you need SQL SELECT statement that returns the primary key from the table. Use the "Enter initial SQL" button to have Inaportinsert a SELECT statement. You will need to complete the WHERE clause.

The SQL may use a parameter that will be replaced by Inaport at run time:

#PARENTKEY will be replaced with the value of the primary key from the matched parent table record

In addition, you may use field names from the incoming source record; Inaportwill replace the field name with the value of the field.

## EXAMPLES

### Match against the account table in Microsoft CRM (use target connector).

SELECT accountid FROM account WHERE name = '#companyname'

### Match against the account table in Microsoft CRM (use source connector).

SELECT accountid FROM mySourceTable WHERE name = '#companyname'

### Match against the account table in Microsoft CRM when you have accountid (use source connector).

#accountid

### Match against the contact table in Microsoft CRM

SELECT contactid FROM contact WHERE parentcustomerid = '#PARENTKEY' and lastname = '#lastname'

### Match against the PERSON table in SageCRM

SELECT pers_personid FROM person WHERE pers_companyid = #PARENTKEY and pers_lastname = '#lastname'