SQL Scripts for Microsoft Dynamics GP: Import Site Bins From CSV

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 allow for the import of bins from a CSV file. The supplied site is validated to ensure it exists before the data is imported; if any errors then errors are presented to the user and the data is not updated.

/*
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 #BIN_IMPORT
	(
		LOCNCODE VARCHAR(10)
		,BIN VARCHAR(15)
	)
GO

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

/*
	VALIDATE DATA
*/
--validate site
INSERT INTO #Errors
	(Error)
--VALUES
	(
		SELECT
			'Site does not exist: ' + CAST(['Import'].LOCNCODE AS VARCHAR(100))
		FROM
			#BIN_IMPORT AS ['Import']
		LEFT JOIN
			IV40700 AS ['Site Setup'] --Site Setup (IV40700)
				ON
					['Site Setup'].LOCNCODE = ['Import'].LOCNCODE
		WHERE
			['Site Setup'].LOCNCODE IS NULL
	)
GO

/*
	INSERT NEW DATA IF NO ERRORS
*/
--insert bom header
IF (SELECT COUNT(*) FROM #Errors) = 0
	-- Insert if no Email Details
	INSERT INTO IV40701 --Site Bin Master (IV40701)
		(
			LOCNCODE
           ,BIN
           ,NOTEINDX
		)
	--VALUES
		(
			SELECT DISTINCT
				LOCNCODE
				,BIN
				,0
			FROM
				#BIN_IMPORT AS ['Import']
			WHERE
				(
					SELECT
						COUNT(['Site Bin Master'].LOCNCODE)
					FROM
						IV40701 AS ['Site Bin Master'] --Site Bin Master (IV40701)
					WHERE
						['Site Bin Master'].LOCNCODE = ['Import'].LOCNCODE
					AND
						['Site Bin Master'].BIN = ['Import'].BIN
				) = 0
		)
GO

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

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

DROP TABLE #Errors
GO