SQL Scripts for Microsoft Dynamics GP: Update Item Engineering File from a Text File

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 uses BULK INSERT to update values in the IVR10015 table for existing items.

/*
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 #IVR10015_IMPORT
	(
		ITEMNMBR VARCHAR(30)
		,[EFFECTIVEDATE_I] VARCHAR(100)
		,[ITEMFULFILLMETHOD] INTEGER
		,[ITEMSTATUS_I] INTEGER
		,[MAKEBUYCODE_I] INTEGER
		,[CALCMRP_I] TINYINT
	)
GO

/*
	BULK INSERT
*/
BULK INSERT
	#IVR10015_IMPORT
FROM
	'c:\temp\ItemEngineering.txt'
WITH
	(FIELDTERMINATOR = '\t'
	,ROWTERMINATOR = '\n'
	,FIRSTROW = 2
)
GO

/*
	VALIDATE DATA
*/
INSERT INTO #Errors
	(Error)
--VALUES
	(
		SELECT
			'Item Engineering record does not exist: ' + CAST(IMPORT.ITEMNMBR AS VARCHAR(100))
		FROM
			#IVR10015_IMPORT AS IMPORT
		LEFT JOIN
			IVR10015 AS ['Item Engineering File'] --IVR10015
				ON
					['Item Engineering File'].ITEMNMBR = IMPORT.ITEMNMBR
		WHERE
			['Item Engineering File'].ITEMNMBR IS NULL
	)
GO

/*
	UPDATE EXISTING DATA IF NO ERRORS
*/
IF (SELECT COUNT(*) FROM #Errors) = 0
		-- UPDATE if Email Details present
	UPDATE
		['Item Engineering File']
	SET
		EFFECTIVEDATE_I = IMPORT.EFFECTIVEDATE_I
		,ITEMFULFILLMETHOD = IMPORT.ITEMFULFILLMETHOD
		,ITEMSTATUS_I = IMPORT.ITEMSTATUS_I
		,MAKEBUYCODE_I = IMPORT.MAKEBUYCODE_I
		,CALCMRP_I = IMPORT.CALCMRP_I
	FROM
		IVR10015 AS ['Item Engineering File'] --IVR10015
	INNER JOIN
		#IVR10015_IMPORT AS IMPORT
			ON
				IMPORT.ITEMNMBR = ['Item Engineering File'].ITEMNMBR
GO

/*
	OUTPUT ERRORS
*/
IF (SELECT COUNT(*) FROM #Errors) > 0
	SELECT Error FROM #Errors ORDER BY ROW_ID
GO

/*
	DROP TEMP TABLES
*/
DROP TABLE #IVR10015_IMPORT
GO

DROP TABLE #Errors
GO