Validate and Insert/Update Vendor Emails in Microsoft Dynamics GP from a Text File

I’ve previously posted a script which could be used to update emails in Microsoft Dynamics GP from a tab delimited text file. I’ve recently been working on a project with a client and created a variation on that script which works only for vendor emails, but validates the data before inserting or updating to ensure that the data is valid.

The original script took it on trust that the data was correct, but this tie we had some concerns about the quality of data being provided so I added validation to ensure the vendor and vendor address both existed before any data was inserted or updated; if there were any errors found, the errors were presented to the user to fix and then rerun the script.

The highlighted section is the path to the text file being uploaded; regardless of where you are running SSMS, this path and file must exist on the SQL Server itself.

/*
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 #SY01200_IMPORT
	(
		VENDORID VARCHAR(100)
		,ADRSCODE VARCHAR(100)
		,EmailToAddress VARCHAR(1000)
		,EmailCcAddress VARCHAR(1000)
		,EmailBccAddress VARCHAR(1000)
	)
GO

/*
	BULK INSERT
*/
BULK INSERT
	#SY01200_IMPORT
FROM
	'c:\temp\email.txt'
WITH
	(
		FIELDTERMINATOR = '\t'
		,ROWTERMINATOR = '\n'
		,FIRSTROW = 2
	)
GO

/*
	VALIDATE DATA
*/
-- validate creditors
INSERT INTO #Errors
	(Error)
	
	(
		SELECT DISTINCT
			'Vendor does not exist: ' + CAST(SYI.VENDORID AS VARCHAR(100))
		FROM
			#SY01200_IMPORT AS SYI
		LEFT JOIN
			PM00200 AS ['PM Creditor Master File']
				ON
					['PM Creditor Master File'].VENDORID = SYI.VENDORID
		WHERE
			['PM Creditor Master File'].VENDORID IS NULL
	)
GO

--validate creditor addresses
INSERT INTO #Errors
	(Error)
	
	(
		SELECT DISTINCT
			'Vendor address does not exist: ' + CAST(SYI.VENDORID AS VARCHAR(100)) + ' / ' + CAST(SYI.ADRSCODE AS VARCHAR(100))
		FROM
			#SY01200_IMPORT AS SYI
		LEFT JOIN
			PM00300 AS ['PM Creditor Addresses']
				ON
					['PM Creditor Addresses'].VENDORID = SYI.VENDORID
				AND
					['PM Creditor Addresses'].ADRSCODE = SYI.ADRSCODE
		WHERE
			['PM Creditor Addresses'].VENDORID IS NULL
	)
GO

/*
	UPDATE EXISTING DATA IF NO ERRORS
*/
IF (SELECT COUNT(*) FROM #Errors) = 0
	UPDATE
		SY
	SET
		SY.EmailToAddress = ISNULL(SY_I.EmailToAddress, '')
		,SY.EmailCcAddress = ISNULL(SY_I.EmailCcAddress, '')
		,SY.EmailBccAddress = ISNULL(SY_I.EmailBccAddress, '')
	FROM
		SY01200 SY
	INNER JOIN
		#SY01200_IMPORT AS SY_I
			ON
				SY_I.VENDORID = SY.Master_ID
			AND
				SY.Master_Type = 'VEN'
			AND
				SY.ADRSCODE = SY_I.ADRSCODE
GO

/*
	INSERT NEW DATA IF NO ERRORS
*/
IF (SELECT COUNT(*) FROM #Errors) = 0
	INSERT INTO SY01200
		(
			Master_Type
			,Master_ID
			,ADRSCODE
			,EmailToAddress
			,EmailCcAddress
			,EmailBccAddress
			,INETINFO
		)

		(
			SELECT
				'VEN'
				,VENDORID
				,ADRSCODE
				,ISNULL(EmailToAddress, '')
				,ISNULL(EmailCcAddress, '')
				,ISNULL(EmailBccAddress, '')
				,''
			FROM
				#SY01200_IMPORT 
			WHERE
				(SELECT
					COUNT(Master_ID)
				FROM
					SY01200
				WHERE
					Master_Type = 'VEN'
				AND
					Master_ID = #SY01200_IMPORT.VENDORID
				AND
					ADRSCODE = #SY01200_IMPORT.ADRSCODE) = 0
				)
GO

/*
	OUTPUT ERRORS
*/
IF (SELECT COUNT(*) FROM #Errors) > 0
	SELECT Error FROM #Errors ORDER BY ROW_ID
GO

/*
	DROP TEMP TABLES
*/
DROP TABLE #SY01200_IMPORT
GO

DROP TABLE #Errors
GO