SQL script to insert emails into Microsoft Dynamics GP

This is a script I wrote years ago and thought I’d posted it here, but it seems it had never been posted, although I have posted a script to insert test emails against all customers. This script can be used to insert or update email addresses on vendors, customers or items in Microsoft Dynamics GP.

The insert doesn’t validate against the records against which the email addresses will be connected, so care does need to be taken to ensure data integrity and the script should be tested first before being run on a live company.

/*
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 TABLE #SY01200_IMPORT
	(
		Master_Type VARCHAR(3) -- VEN = Vendor / CUS = Customer / ITM = Item
		,Master_ID VARCHAR(100)
		,ADRSCODE VARCHAR(100)
		,EmailToAddress VARCHAR(1000)
		,EmailCcAddress VARCHAR(1000)
		,EmailBccAddress VARCHAR(1000)
	)
GO

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

-- UPDATE if Email Details present
UPDATE
	SY
SET
	SY.EmailToAddress = SY_I.EmailToAddress
	,SY.EmailCcAddress = SY_I.EmailCcAddress
	,SY.EmailBccAddress = SY_I.EmailBccAddress
FROM
	SY01200 SY
INNER JOIN
	#SY01200_IMPORT AS SY_I
		ON
			SY_I.Master_ID = SY.Master_ID
		AND
			SY.Master_Type = SY_I.Master_Type
		AND
			SY.ADRSCODE = SY_I.ADRSCODE
GO

-- Insert if no Email Details
INSERT INTO SY01200
	(
		Master_Type
		,Master_ID
		,ADRSCODE
		,EmailToAddress
		,EmailCcAddress
		,EmailBccAddress
	)

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

DROP TABLE #SY01200_IMPORT
GO