This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.
This script is the customer equivalent of the ,a href=https://www.azurecurve.co.uk/2021/07/validate-and-insert-update-vendor-emails-in-microsoft-dynamics-gp-from-a-text-file/’>vendor script I posted a while ago; it uses BULK INSERT to upload a text file and updates the email addresses on the supplied customer and address combinations.
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
/*
CREATE TEMP ERROR TABLE
*/
CREATE TABLE #Errors
(
Error VARCHAR(1000)
,ROW_ID INT IDENTITY
)
GO
/*
CREATE TEMP TABLE FOR DATA
*/
CREATE TABLE #CUSTOMER_IMPORT
(
CUSTNMBR VARCHAR(30)
,ADRSCODE VARCHAR(30)
,EmailToAddress VARCHAR(230)
,EmailCcAddress VARCHAR(230)
,EmailBccAddress VARCHAR(230)
)
GO
/*
BULK INSERT
*/
BULK INSERT
#CUSTOMER_IMPORT
FROM
'C:\Temp\Customer Address Emails.txt'
WITH
(FIELDTERMINATOR = '\t'
,ROWTERMINATOR = '\n'
,FIRSTROW = 2
)
GO
/*
VALIDATE DATA
*/
--validate parent item
INSERT INTO #Errors
(Error)
--VALUES
(
SELECT
'Customer Address does not exist: ' + CAST(['Import'].CUSTNMBR AS VARCHAR(100)) + ' / ' + CAST(['Import'].ADRSCODE AS VARCHAR(100))
FROM
#CUSTOMER_IMPORT AS ['Import']
LEFT JOIN
RM00101 AS ['RM Customer Address Master'] --RM Customer MSTR (RM00101)
ON
['RM Customer Address Master'].CUSTNMBR = ['Import'].CUSTNMBR
AND
['RM Customer Address Master'].ADRSCODE = ['Import'].ADRSCODE
WHERE
['RM Customer Address Master'].ADRSCODE IS NULL
AND
['Import'].EmailToAddress IS NOT NULL
)
GO
/*
INSERT/UPDATE EMAIL DATA IF NO ERRORS
*/
--insert emails
IF (SELECT COUNT(*) FROM #Errors) = 0
BEGIN
-- UPDATE if Email Details present
UPDATE
SY
SET
SY.EmailToAddress = SY_I.EmailToAddress
,SY.EmailCcAddress = ISNULL(SY_I.EmailCcAddress, '')
,SY.EmailBccAddress = ISNULL(SY_I.EmailBccAddress, '')
FROM
SY01200 SY --Internet Addresses (SY01200)
INNER JOIN
#CUSTOMER_IMPORT AS SY_I
ON
SY_I.CUSTNMBR = SY.Master_ID
AND
SY.Master_Type = 'CUS'
AND
SY.ADRSCODE = SY_I.ADRSCODE
WHERE
SY_I.EmailToAddress IS NOT NULL
-- Insert if no Email Details
INSERT INTO SY01200
(
Master_Type
,Master_ID
,ADRSCODE
,INETINFO
,EmailToAddress
,EmailCcAddress
,EmailBccAddress
)
(
SELECT
'CUS'
,CUSTNMBR
,ADRSCODE
,''
,EmailToAddress
,ISNULL(EmailCcAddress, '')
,ISNULL(EmailBccAddress, '')
FROM
#CUSTOMER_IMPORT AS SY_I
WHERE
SY_I.EmailToAddress IS NOT NULL
AND
(SELECT
COUNT(Master_ID)
FROM
SY01200
WHERE
Master_Type = 'CUS'
AND
Master_ID = SY_I.CUSTNMBR
AND
ADRSCODE = SY_I.ADRSCODE) = 0
)
END
GO
/*
OUTPUT ERRORS
*/
IF (SELECT COUNT(*) FROM #Errors) > 0
SELECT Error FROM #Errors ORDER BY ROW_ID
GO
/*
DROP TEMP TABLES
*/
DROP TABLE #CUSTOMER_IMPORT
GO
DROP TABLE #Errors
GO
