I’ve previously posted a script which could be used to update emails in Microsoft Dynamics GP from a tab delimited text file. I’ve recently been working on a project with a client and created a variation on that script which works only for vendor emails, but validates the data before inserting or updating to ensure that the data is valid.
The original script took it on trust that the data was correct, but this tie we had some concerns about the quality of data being provided so I added validation to ensure the vendor and vendor address both existed before any data was inserted or updated; if there were any errors found, the errors were presented to the user to fix and then rerun the script.
The highlighted section is the path to the text file being uploaded; regardless of where you are running SSMS, this path and file must exist on the SQL Server itself.
/*
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 TEMP ERROR TABLE
*/
CREATE TABLE #Errors
(
Error VARCHAR(1000)
,ROW_ID INT IDENTITY
)
GO
/*
CREATE TEMP TABLE FOR DATA
*/
CREATE TABLE #SY01200_IMPORT
(
VENDORID VARCHAR(100)
,ADRSCODE VARCHAR(100)
,EmailToAddress VARCHAR(1000)
,EmailCcAddress VARCHAR(1000)
,EmailBccAddress VARCHAR(1000)
)
GO
/*
BULK INSERT
*/
BULK INSERT
#SY01200_IMPORT
FROM
'c:\temp\email.txt'
WITH
(
FIELDTERMINATOR = '\t'
,ROWTERMINATOR = '\n'
,FIRSTROW = 2
)
GO
/*
VALIDATE DATA
*/
-- validate creditors
INSERT INTO #Errors
(Error)
(
SELECT DISTINCT
'Vendor does not exist: ' + CAST(SYI.VENDORID AS VARCHAR(100))
FROM
#SY01200_IMPORT AS SYI
LEFT JOIN
PM00200 AS ['PM Creditor Master File']
ON
['PM Creditor Master File'].VENDORID = SYI.VENDORID
WHERE
['PM Creditor Master File'].VENDORID IS NULL
)
GO
--validate creditor addresses
INSERT INTO #Errors
(Error)
(
SELECT DISTINCT
'Vendor address does not exist: ' + CAST(SYI.VENDORID AS VARCHAR(100)) + ' / ' + CAST(SYI.ADRSCODE AS VARCHAR(100))
FROM
#SY01200_IMPORT AS SYI
LEFT JOIN
PM00300 AS ['PM Creditor Addresses']
ON
['PM Creditor Addresses'].VENDORID = SYI.VENDORID
AND
['PM Creditor Addresses'].ADRSCODE = SYI.ADRSCODE
WHERE
['PM Creditor Addresses'].VENDORID IS NULL
)
GO
/*
UPDATE EXISTING DATA IF NO ERRORS
*/
IF (SELECT COUNT(*) FROM #Errors) = 0
UPDATE
SY
SET
SY.EmailToAddress = ISNULL(SY_I.EmailToAddress, '')
,SY.EmailCcAddress = ISNULL(SY_I.EmailCcAddress, '')
,SY.EmailBccAddress = ISNULL(SY_I.EmailBccAddress, '')
FROM
SY01200 SY
INNER JOIN
#SY01200_IMPORT AS SY_I
ON
SY_I.VENDORID = SY.Master_ID
AND
SY.Master_Type = 'VEN'
AND
SY.ADRSCODE = SY_I.ADRSCODE
GO
/*
INSERT NEW DATA IF NO ERRORS
*/
IF (SELECT COUNT(*) FROM #Errors) = 0
INSERT INTO SY01200
(
Master_Type
,Master_ID
,ADRSCODE
,EmailToAddress
,EmailCcAddress
,EmailBccAddress
,INETINFO
)
(
SELECT
'VEN'
,VENDORID
,ADRSCODE
,ISNULL(EmailToAddress, '')
,ISNULL(EmailCcAddress, '')
,ISNULL(EmailBccAddress, '')
,''
FROM
#SY01200_IMPORT
WHERE
(SELECT
COUNT(Master_ID)
FROM
SY01200
WHERE
Master_Type = 'VEN'
AND
Master_ID = #SY01200_IMPORT.VENDORID
AND
ADRSCODE = #SY01200_IMPORT.ADRSCODE) = 0
)
GO
/*
OUTPUT ERRORS
*/
IF (SELECT COUNT(*) FROM #Errors) > 0
SELECT Error FROM #Errors ORDER BY ROW_ID
GO
/*
DROP TEMP TABLES
*/
DROP TABLE #SY01200_IMPORT
GO
DROP TABLE #Errors
GO
