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