MatchUp Object:SQL-CLR:Functions

From Melissa Data Wiki
Jump to navigation Jump to search

← 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