SQL script to insert emails into Microsoft Dynamics GP

Microsoft Dynamics GPThis 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