SQL script to insert test email address to all customers

Back when this blog was a few months old, I posted a script on updating emails on vendors to keep emails internal for testing and have recently had the need to provide a customer a similar script for testing the emailing of invoices and statements.

This first script updates all existing records to replace the email address with one supplied by the customer (the highlighted email address should be changed to the required email address):

/*
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).
*/
DECLARE @TestEmail VARCHAR(100)
SET @TestEmail = 'email@example.com'

UPDATE
	SY01200
SET
	INET1 = @TestEmail
	,EmailToAddress = @TestEmail
	,EmailCcAddress = ''
	,EmailBccAddress = ''
WHERE
	Master_Type = 'CUS'

this second script will insert an email address against all customer addresses which don’t currently have an email (the highlighted email address should be changed to the required email address).

/*
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).
*/
DECLARE @TestEmail VARCHAR(100)
SET @TestEmail = 'email@example.com'

INSERT INTO SY01200
	(
		Master_Type
		,Master_ID
		,ADRSCODE
		,INETINFO
		,EmailToAddress
		,EmailCcAddress
		,EmailBccAddress
	)
--VALUES
	(
		SELECT 
			'CUS'
			,CUSTNMBR
			,ADRSCODE
			,''
			,@TestEmail
			,''
			,''
		FROM
			RM00102 AS ['Customer Master Address File']
		WHERE
			(
				SELECT
					COUNT(Master_ID)
				FROM
					SY01200 AS ['Internet Addresses']
				WHERE
					Internet Addresses.Master_Type = 'CUS'
				AND
					Internet Addresses.Master_ID = ['Customer Master Address File'].CUSTNMBR
				AND
					Internet Addresses.ADRSCODE = ['Customer Master Address File'].ADRSCODE
			) = 0
	)

These scripts are only intended for use on a development or test system so please be careful when running them; as always with update scripts, I’d recommend having a good backup before running them just in case of problems.