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
