Bulk Updating "Sales Person" assigned to Customers

Overview

This article describes the procedure to update the assigned "Sales Person" for a list of customer accounts through a bulk operation on the database.

Solution

The solution steps provided assume that the customer has the desired "Sales Person" assignment list available in an Excel file having the attributes shown below.

Some sample data is included for demonstration:

Follow the steps outlined below to update the assigned Sales Person for the listed customer accounts with the names provided in the highlighted column:

1. Create a temporary table named temp_cust to copy the Excel data:
   

   /****** Object:  Table [dbo].[temp_cust]    Script Date: 10/27/2020 1:05:11 AM ******/
   SET ANSI_NULLS ON
   GO
   
   SET QUOTED_IDENTIFIER ON
   GO
   
   CREATE TABLE [dbo].[temp_cust](
    [CUST_CODE] [dbo].[T_VARCHAR_15] NULL,
    [Country] [dbo].[T_VARCHAR_40] NULL,
    [NAME] [dbo].[T_VARCHAR_40] NULL,
    [LOC_Sub] [dbo].[T_VARCHAR_40] NULL,
    [Sales_P] [dbo].[T_VARCHAR_40] NULL,
    [Currency_Code] [dbo].[T_VARCHAR_40] NULL,
    [Customer_level] [dbo].[T_VARCHAR_40] NULL
   ) ON [PRIMARY]
   GO

2. Import the Excel date to the temp table
   
   Tip: You may use any of the methods described in this external article to import the Excel data.
3. Backup the Custtable into the Support_Cust_2408395 table
4. Execute the following UPDATE statement:
   

   Update C
     Set C.SALESPERSO =  t.Sales_P
     from Cust C inner join temp_cust t on t.cust_code=c.cust_code

   Note:
   The above query should be used if Sales Person IDCODE is used in the data; otherwise, if the Sales Person Name is used in the data, use the following update statement instead:
   

   Update C
     Set C.SALESPERSO =  (Select top 1 IDno from PERSONAL where PERSONAL.pfirst =t.Sales_P)
     from Cust C inner join temp_cust t on t.cust_code=c.cust_code
     inner join Personal P on P.PFIRST= t.sales_P

Testing

Check the customer accounts from the UI to confirm that they are accordingly updated to reflect the new Sales Person as assigned.

Back to top