Inaport Training SQL Matching Matching Process of deciding

Inaport Training SQL Matching

Matching • Process of deciding which record or set of records in the target table(s) should be updated • Alternatively, decide if record already exists and take appropriate action © Copyright 2010 Ina. Plex Inc

Matching Techniques Inaport supports different ways to match • Standard • build expressions on source and target • Fuzzy • Refine Standard to allow for poor data • SQL • Use SQL SELECT instead of expressions © Copyright 2010 Ina. Plex Inc

Matching Both Standard and Fuzzy matching • Are flexible, powerful, easy to use • Build an in memory index The in memory index has benefits and drawbacks • Fast once it is built, BUT • Up front cost of building (read data, build index) - and • On-going memory cost For large (>2 -400, 000 records) these up front costs may be significant © Copyright 2010 Ina. Plex Inc

SQL Matching SQL matching is an alternative scheme • Executes SQL SELECT against the target table for each incoming record • Benefits • No up front cost • No memory cost • Drawbacks • Not as flexible – restricted to SQL • SQL cost per incoming record © Copyright 2010 Ina. Plex Inc

How it Works You specify a SQL SELECT statement that will • read the primary key of the target table, • with a WHERE clause that implements your match criteria SELECT accountid FROM account WHERE account = ‘#Company. Name’ © Copyright 2010 Ina. Plex Inc

How it Works SELECT accountid FROM account WHERE account = ‘#Company. Name’ At run time, Inaport will: • Parse the SQL and replace any field names with the field value • ‘#Company. Name’ becomes ‘Ina. Plex’ • Run SQL against target • Use primary keys found as matches © Copyright 2010 Ina. Plex Inc

How it Works The only requirement on SQL is that it return the primary key value Can query a different table: SELECT accountid FROM account_extra WHERE field 1= ‘#my. Value’ © Copyright 2010 Ina. Plex Inc

How it Works When working with a child table, WHERE clause must include match on foreign key SELECT contactid FROM contact WHERE accountid = ‘#PARENTKEY’ AND lastname = ‘#Last. Name’ Inaport will replace ‘#PARENTKEY’ with the primary key of the matched parent record (e. g. accountid) SELECT contactid FROM contact WHERE accountid = ‘A 12345678’ AND lastname = ‘Smith’ © Copyright 2010 Ina. Plex Inc

Example – Operations Tab Select SQL Match from the Match type © Copyright 2010 Ina. Plex Inc

Example – Relationships Tab Set the primary and foreign keys on the Relationships Tab Normally accept the Inaport defaults © Copyright 2010 Ina. Plex Inc

Example – Match Tab On Match Tab, click “Enter Initial SQL” and Inaport will write SQL Then complete the WHERE clause © Copyright 2010 Ina. Plex Inc

Run Time At run time • Source record comes in • Inaport will generate SQL, changing ‘#field. Name’ to ‘value of field’ • SQL will be executed against target table • Primary keys returned will be sued as matches © Copyright 2010 Ina. Plex Inc

Benefits • No up front cost of building indexes • Run starts immediately • No memory cost of in memory indexes • For large target tables, and/or small import runs, speed can be greatly improved © Copyright 2010 Ina. Plex Inc

Drawbacks • Can only be used with targets that support SQL • Not ACT! • Expressions restricted to SQL • No normcomp(), regular expressions • Requires understanding of SQL • May require database tuning • SQL executed for each incoming record • Search on non-indexed field can have significant SQL Server performance impact © Copyright 2010 Ina. Plex Inc

Summary SQL Matching is good fit: • Target table is much larger than import table, or • Target table is very large • Depends on system, but > 2 -400, 000 records Remember • You can mix and match – use different match techniques on different tables © Copyright 2010 Ina. Plex Inc

THANK YOU www. inaplex. com/cs/forums © Copyright 2010 Ina. Plex Inc