SQL Scripts for Microsoft Dynamics GP: Validate and Insert/Update Vendor Emails from a Text File

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