Day: December 19, 2022

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script will take a CSV file and update the three phone numbers and fax numbers on the vendor address supplied.

/*
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 #PM00200_IMPORT
	(
		VENDORID VARCHAR(100)
		,ADRSCODE VARCHAR(100)
		,PHNUMBR1 VARCHAR(100)
		,PHNUMBR2 VARCHAR(100)
		,PHONE3 VARCHAR(100)
		,FAXNUMBR VARCHAR(100)
	)
GO

BULK INSERT
	#PM00200_IMPORT
FROM
	'C:\Temp\Supplier Phone and Fax.csv'
WITH
	(
		FIELDTERMINATOR = ','
		,ROWTERMINATOR = '\n'
		,FIRSTROW = 2
	)
GO

select * from #PM00200_IMPORT

-- VENDOR MASTER
UPDATE
	PM
SET
	PM.PHNUMBR1 = ISNULL(PM_I.PHNUMBR1,'')
	,PM.PHNUMBR2 = ISNULL(PM_I.PHNUMBR2,'')
	,PM.PHONE3 = ISNULL(PM_I.PHONE3,'')
	,PM.FAXNUMBR = ISNULL(PM_I.FAXNUMBR,'')
FROM
	PM00200 PM
INNER JOIN
	#PM00200_IMPORT As PM_I
		ON UPPER(PM_I.ADRSCODE) = PM.VENDORID
	AND
		PM_I.ADRSCODE = "MAIN"
GO

-- VENDOR ADDRESS MASTER
UPDATE
	PM
SET
	PM.PHNUMBR1 = ISNULL(PM_I.PHNUMBR1,'')
	,PM.PHNUMBR2 = ISNULL(PM_I.PHNUMBR2,'')
	,PM.PHONE3 = ISNULL(PM_I.PHONE3,'')
	,PM.FAXNUMBR = ISNULL(PM_I.FAXNUMBR,'')
FROM
	PM00300 PM
INNER JOIN
	#PM00200_IMPORT As PM_I
		ON PM_I.VENDORID = PM.VENDORID AND PM.ADRSCODE = UPPER(PM_I.ADRSCODE)
GO

DROP TABLE #PM00200_IMPORT
GO