Admin: Created page with "{{MatchUpSQLCLRNav}} {{CustomTOC}} ==mdMUMatchTable== ;Compares an Input Table to a Master Table
 mdMUMatchTable(, , , 
2015-07-16T18:19:06Z
Created page with "{{MatchUpSQLCLRNav}}  {{CustomTOC}}  ==mdMUMatchTable== ;Compares an Input Table to a Master Table  <pre> mdMUMatchTable(<Config File>, <Input Table>, <Master Table>, <Id Fiel..."
New page
{{MatchUpSQLCLRNav}}



{{CustomTOC}}



==mdMUMatchTable==

;Compares an Input Table to a Master Table



<pre>

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

</pre>



{|class="alternate01" cellspacing="0"

!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:



{|class="alternate01" cellspacing="0"

!Id!!Result!!Dupes!!DupeNo!!Key

|-

|1||MS03,MS06||2||0||style="padding:4px;"|<pre style="margin:0;">01845DAVID     SALINAS   150  FLAGSHIP            </pre>

|-

|58||MS02,MS06||0||1||style="padding:4px;"|<pre style="margin:0;">01845DAVID     SALINAS   150  FLAGSHIP            </pre>

|-

|59||MS02,MS06||0||2||style="padding:4px;"|<pre style="margin:0;">01845DAVID     SALINAS   150  FLAGSHIP            </pre>

|-

|2||MS03,MS06||1||0||style="padding:4px;"|<pre style="margin:0;">01851BRIAN     HARRISON  120  STEDMAN             </pre>

|-

|65||MS02,MS06||0||1||style="padding:4px;"|<pre style="margin:0;">01851BRIAN     HARRISON  120  STEDMAN             </pre>

|-

|3||MS03,MS06||1||0||style="padding:4px;"|<pre style="margin:0;">01864EVERETT   DICKY     35   CONCORD             </pre>

|}





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:



<pre>

SELECT * FROM mdMUMatchTable(‘TestMatch.cfg’, ‘Candidates’, ‘Master’, ‘Id’) 

  WHERE Left(Result, 4) = ‘MS03’ AND Dupes = 0

</pre>



==mdMUMatchRecord==

;Compares a Single Record to a Master Table



<pre>

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

</pre>



{|class="alternate01" cellspacing="0"

!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|mdMUMatchTable]] for details on this cursor.



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



<pre>

IF (SELECT Count(*) FROM 

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



  -- Record already exists in Master



END

</pre>



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



<pre>

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

</pre>



{|class="alternate01" cellspacing="0"

!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:



<pre>

CREATE TRIGGER TR_Master ON Master INSTEAD OF INSERT

  AS EXTERNAL NAME MatchUpSQL.MelissaDataDQT.mdMUInsertTrigger

</pre>



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:



<pre>

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

</pre>





[[Category:MatchUp Object]]
MatchUp Object:SQL-CLR:Functions - Revision history
