Update Debtor Addresses From CSV To Work Around An Integration Manager Bug

Earlier this week I posted about a bug in Integration Manager where it added extra zeros into the phone and fax number fields when importing creditors. Well, the same bug also affects debtors but a similar script to update debtors via a CSV is also possible.

The following script is basically the same script I used for creditors with the table and column names changed where necessary;

CREATE TABLE #NewDebtorAddressMaster
   (CUSTNMBR VARCHAR(100)
   ,ADRSCODE VARCHAR(100)
   ,PHONE1 VARCHAR(100)
   ,PHONE2 VARCHAR(100)
   ,PHONE3 VARCHAR(100)
   ,FAX VARCHAR(100))
GO

BULK INSERT
   #NewDebtorAddressMaster
FROM
   'c:\temp\debtors.csv'
WITH
   (FIELDTERMINATOR = ','
   ,ROWTERMINATOR = '\n')
GO

UPDATE
   ['RM Address Master']
SET
   PHONE1 = Left(#NewDebtorAddressMaster.PHONE1, 14)
   ,PHONE2 = Left(#NewDebtorAddressMaster.PHONE2, 14)
   ,PHONE3 = Left(#NewDebtorAddressMaster.PHONE3, 14)
   ,FAX = Left(#NewDebtorAddressMaster.FAX, 14)
FROM
   RM00102 AS ['RM Address Master']
INNER JOIN
   #NewDebtorAddressMaster 
      ON #NewDebtorAddressMaster.CUSTNMBR = ['RM Address Master'].CUSTNMBR 
         AND #NewDebtorAddressMaster.ADRSCODE = ['RM Address Master'].ADRSCODE

UPDATE
   ['RM Debtor Master']
SET
   PHONE1 = LEFT(['RM Address Master'].PHONE1, 14)
   ,PHONE2 = LEFT(['RM Address Master'].PHONE2, 14)
   ,PHONE3 = LEFT(['RM Address Master'].PHONE3, 14)
   ,FAX = LEFT(['RM Address Master'].FAX, 14)
FROM
   RM00101 AS ['RM Debtor Master']
INNER JOIN
   RM00102 AS ['RM Address Master']
      ON ['RM Address Master'].CUSTNMBR = ['RM Debtor Master'].CUSTNMBR
         AND ['RM Address Master'].ADRSCODE = ['RM Debtor Master'].ADRSCODE

DROP TABLE #NewDebtorAddressMaster

If you use this script then, as always when running a script, please make sure you have a good backup of your company database (I define a good backup as one you have restored to ensure it backed up correctly) and know what you are doing in Management Studio with SQL Statements.