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
