SQL Script to update vendor and customer email addresses

Microsoft Dynamics GPBack in August 2011 I posted a SQL script which transfers vendor email addresses from the INET1 to the Email Address field and today’s script is a variation of that one.

The original script I posted only did a transfer for vendors and would migrate the INET1 one field regardless of whether there was already something in the Email Address field.

This new script only does the transfer if INET1 is longer than 1 character and the Email Address field is empty.

It also sets the Email Bcc Field to a supplied 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). */
UPDATE SY01200 SET EmailToAddress = INET1 ,EmailBccAddress = 'purchase.archive@example.com' WHERE Master_Type = 'VEN' AND LEN(CAST(INET1 AS VARCHAR(100))) > 0 AND LEN(CAST(EmailToAddress AS VARCHAR(100))) = 0 GO UPDATE SY01200 SET EmailToAddress = INET1 ,EmailBccAddress = 'sales.archive@example.com' WHERE Master_Type = 'CUS' AND LEN(CAST(INET1 AS VARCHAR(100))) > 0 AND LEN(CAST(EmailToAddress AS VARCHAR(100))) = 0 GO