MatchUp Object:SQL-CLR:Functions

← MatchUp Object

MatchUp SQL-CLR Navigation
Overview
Tutorial
Functions

mdMUMatchTable

Compares an Input Table to a Master Table

mdMUMatchTable(<Config File>, <Input Table>, <Master Table>, <Id Field>)

Name	Description
<Config File>	Name of configuration file (from SSIS step).
<Input Table>	Name of Input Table.
<Master Table>	Name of Master Table.
<Id Field>	Name of ID field (optional, specify ‘’ if not desired).

This function returns a cursor containing each record from <Input Table>. Following each record are the record(s) in <Master Table> that matched the record. For example:

Id	Result	Dupes	DupeNo	Key
1	MS03,MS06	2	0	01845DAVID SALINAS 150 FLAGSHIP
58	MS02,MS06	0	1	01845DAVID SALINAS 150 FLAGSHIP
59	MS02,MS06	0	2	01845DAVID SALINAS 150 FLAGSHIP
2	MS03,MS06	1	0	01851BRIAN HARRISON 120 STEDMAN
65	MS02,MS06	0	1	01851BRIAN HARRISON 120 STEDMAN
3	MS03,MS06	1	0	01864EVERETT DICKY 35 CONCORD

Records with result codes containing MS01 (unique) or MS03 (has duplicates) represent records from <Input Table>, ones containing MS02 (dupe) represent records from <Master Table>. <Input Table> records will report the number of duplicates in the Dupes field. The DupeNo field will contain an index of each duplicate record.

You can use a SELECT to determine which records could be inserted into the Master table. For example:

SELECT * FROM mdMUMatchTable(‘TestMatch.cfg’, ‘Candidates’, ‘Master’, ‘Id’) 
  WHERE Left(Result, 4) = ‘MS03’ AND Dupes = 0

mdMUMatchRecord

Compares a Single Record to a Master Table

mdMUMatchRecord (<Config File>, <Input Fields>, <Master Table>, <Id Field>)

Name	Description
<Config File>	Name of configuration file (from SSIS step).
<Input Fields>	Pipe-delimited list of field contents.
<Master Table>	Name of Master Table.
<Id Field>	Name of ID field (optional, specify ‘’ if not desired).

This function returns a cursor (having same schema as the one returned by [[#mdMUMatchTable|mdMUMatchTable) containing the single record followed by record(s) in <Master Table> that matched the record. See mdMUMatchTable for details on this cursor.

You can use a SELECT to determine if a record matches one in the Master table. For example:

IF (SELECT Count(*) FROM 
  mdMUMatchRecord(‘TestMatch.cfg’, ‘02346|John Smith|JohnSmith’, ‘Master’, ‘Id’)) <> 0 BEGIN

  -- Record already exists in Master

END

In an interactive application, this function can be used to generate a list of ‘possible matches’ for a CSR as they enter in new client information.

mdMUInsertTable

Inserts Unique Records from an Input Table into a Master Table

EXEC mdMUInsertTable <retVal> OUT, <Config File>, <Input Table>, <Master Table>

Name	Description
<retVal>	Return value (see below).
<Config File>	Name of configuration file (from SSIS step).
<Input Fields>	Pipe-delimited list of field contents.
<Master Table>	Name of Master Table.

This procedure inserts all unique records in <Input Table> into <Master Table>, using the information specified in <Config File>. Note that <Input Table> does not need to have a matchcode key field, as matchcodes will be built for records in <Input Table> by this procedure (they do have to exist in <Master Table>, though). When a record from <Input Table> is inserted, all matching fields will be updated in the record in <Master Table>, as well as the matchcode key field.

This is a simple way to insert unique records.

mdMUInsertTrigger

Insert Trigger for Master Table

An INSERT trigger will execute whenever any user attempts to insert a record in any fashion. Insert triggers are used a bit differently than the other functions:

CREATE TRIGGER TR_Master ON Master INSTEAD OF INSERT
  AS EXTERNAL NAME MatchUpSQL.MelissaDataDQT.mdMUInsertTrigger

Because the CREATE TRIGGER syntax has no accommodation for parameters, you cannot specify the name of a configuration file here. Instead, mdMUInsertTrigger looks for a configuration file having the same name as the trigger, located in c:\ProgramData\Melissa DATA\MatchUp. In the above example, the file:

c:\ProgramData\Melissa DATA\MatchUp\TR_Master.cfg