Overview

This article provides the steps to import bulk contacts and activities into the database without manually matching each one in the user interface.

Information

If you want to proceed with the queries mentioned in the following section, please make sure you back up your entire database. Once you execute the queries listed below, the data will be lost and will be unrecoverable. You can use the method described in the Backing Up Tables for Security KB Article for this purpose.

Input information

To get these records eliminated from the database, you must first reunite the following information:

1. Branch where the incoming contacts, which needs to be auto-inserted, are placed.
2. (Optional) Any specific branch/database where these contacts need to be placed

Bear in mind that some entries may still need manual matching after the auto-insertion is completed.

To bulk import contacts and activities into the database, complete the following steps:

1. Confirm the branch that these customers belong to.
2. Check for pending matches in the 'Unmatched' tool and get a few examples.
   
3. Identify a way to select these contacts from the Exceptions database, based on Branch/region details:
   

   select USER_ID from users where Branch_ID ='<Branch id number>'
   
   select Branch_ID from Branches where Region_ID in
     (
     select Region_ID from Regions where Region_Name like '%<name of the region>%'
     )
   

4. Connect to the database.
5. Run the following DB scripts to transfer contacts and activities from the Exceptions Database (<name of the exceptions database>.dbo) to the Bank Database (<name of the bank database>.dbo) (Make sure you remove the comments signs).
   

   declare @Activities TABLE (
   ActivityID uniqueidentifier)
   
   declare @Contacts TABLE (
   ContactID uniqueidentifier)
   
   BEGIN
   
   insert @Contacts
   select Contact_ID from <name of the exceptions database>.dbo.Contacts where Contact_ID in 
    (select Contact_ID from <name of the exceptions database>.dbo.Activity_Contacts where Activity_ID in 
     (select Activity_ID from <name of the exceptions database>.dbo.activity where USER_ID in 
         (select USER_ID from <name of the bank database>.dbo.Users where Branch_ID ='<Branch ID Number>'))) 
   
   insert @Activities
   select Activity_ID from <name of the exceptions database>.dbo.activity where Activity_ID in 
    (Select Activity_ID from <name of the exceptions database>.dbo.Activity_Contacts where Contact_ID in 
     (select ContactID from @Contacts))
    
   
   /* count */    
   ----1
   --    select count (*) from <name of the exceptions database>.dbo.Contacts where Contact_ID in 
   --    (select ContactID from @Contacts) 
   ----2    
   --    select count (*) from <name of the exceptions database>.dbo.Activity where Activity_ID in 
   --    (select ActivityID from @Activities) 
   ----3    
   --    select count (*) from <name of the exceptions database>.dbo.Activity_Contacts where Activity_ID in 
   --    (select ActivityID from @Activities) 
   --    and Activity_Contact_ID not in (select Activity_Contact_ID from <name of the bank database>.dbo.Activity_Contacts)
   ----4
   --    select count (*) from <name of the exceptions database>.dbo.Activity_Detail where Activity_ID in 
   --    (select ActivityID from @Activities) 
   ----5    
   --    select count (*) from <name of the exceptions database>.dbo.activity_detail where activity_detail_id in 
   --    (select activity_detail_id from <name of the bank database>.dbo.activity_detail)
   ----6
   --    select count (*) from <name of the exceptions database>.dbo.activity_contacts where activity_contact_id in 
   --    (select activity_contact_id from <name of the bank database>.dbo.activity_contacts)
   ----7
   --    select count (*) from <name of the exceptions database>.dbo.activity where activity_id in 
   --    (select activity_id from <name of the bank database>.dbo.activity)
   ----8
   --    select count (*) from <name of the exceptions database>.dbo.contact_profiles where contact_id in 
   --    (select ContactID from @Contacts)
   ----9
   --    select count (*) from <name of the exceptions database>.dbo.user_contacts where contact_id in 
   --    (select ContactID from @Contacts)
   ----10
   --    select count (*) from <name of the exceptions database>.dbo.contacts where contact_id in 
   --    (select contact_id from <name of the bank database>.dbo.contacts)
    
   /* backup */    
   ----1    
   --    select * into <name of the bank database>.dbo.Contacts_BK_<date backup created>
   --  from <name of the exceptions database>.dbo.Contacts where Contact_ID in 
   --    (select ContactID from @Contacts) 
   ----2
   --    select * into <name of the bank database>.dbo.Activity_BK_<date backup created>
   --  from <name of the exceptions database>.dbo.Activity where Activity_ID in 
   --    (select ActivityID from @Activities)
   ----3
   --    select * into <name of the bank database>.dbo.Activity_Contacts_BK_<date backup created>
   --  from <name of the exceptions database>.dbo.Activity_Contacts where Activity_ID in 
   --    (select ActivityID from @Activities) 
   --    and Activity_Contact_ID not in (select Activity_Contact_ID from <name of the bank database>.dbo.Activity_Contacts)
   ----4
   --    select * into <name of the bank database>.dbo.Activity_Detail_BK_<date backup created>
   --  from <name of the exceptions database>.dbo.Activity_Detail where Activity_ID in 
   --    (select ActivityID from @Activities) 
    
   /* Insert Unmatched
    run each of the 4 steps separately, running all might consume some time */    
   ----1    
   -- insert <name of the bank database>.dbo.Contacts
   -- select * from <name of the exceptions database>.dbo.Contacts where Contact_ID in 
   -- (select ContactID from @Contacts) 
   ----2
   -- insert <name of the bank database>.dbo.Activity
   -- select * from <name of the exceptions database>.dbo.Activity where Activity_ID in 
   -- (select ActivityID from @Activities) 
   ----3
   -- insert <name of the bank database>.dbo.Activity_Contacts
   -- select * from <name of the exceptions database>.dbo.Activity_Contacts where Activity_ID in 
   -- (select ActivityID from @Activities)
   -- and  contact_id  in (select contact_id from [<name of the bank database>].[dbo].[Contacts])
   ----4
   -- insert <name of the bank database>.dbo.Activity_Detail
   -- select * from <name of the exceptions database>.dbo.Activity_Detail where Activity_ID in 
   -- (select ActivityID from @Activities)
    
   /* Delete Unmatched */    
    
   ----5
   --    delete <name of the exceptions database>.dbo.activity_detail where activity_detail_id in 
   --    (select activity_detail_id from <name of the bank database>.dbo.activity_detail)
   ----6
   --    delete <name of the exceptions database>.dbo.activity_contacts where activity_contact_id in 
   --    (select activity_contact_id from <name of the bank database>.dbo.activity_contacts)
   ----7
   --    delete <name of the exceptions database>.dbo.activity where activity_id in 
   --    (select activity_id from <name of the bank database>.dbo.activity)
   ----8
   --    delete <name of the exceptions database>.dbo.contact_profiles where contact_id in 
   --    (select ContactID from @Contacts)
   ----9
   --    delete <name of the exceptions database>.dbo.user_contacts where contact_id in 
   --    (select ContactID from @Contacts)
   ----10
   --    delete <name of the exceptions database>.dbo.contacts where contact_id in 
   --    (select contact_id from <name of the bank database>.dbo.contacts)
   END

Once the program and backend work has finished, all the information will be imported. If you want support to execute the backend modifications on your behalf, please contact the Support staff and provide them with the full information and description of this case. A customer support representative will contact you back with the result of the execution of the aforementioned query.

<supportagent>

If the customer creates a ticket requesting this task, execute the queries and when the backend work has finished, complete the following steps:

1. Create a table from the output from “Count”:
   

   Name	New Records
   Contacts	WWWW
   Activities	XXXX
   Activity_Contacts	YYYY
   Activity_Details	ZZZZ

2. Share the Before and After Unmatched Pool screenshot with the customer (example below)
   
   Before:
   
   
   After:
   
   
   
3. Advise that the remaining unmatched accounts need to be matched manually by the customer.

</supportagent>