SQL Scripts for Microsoft Dynamics GP: Upload and Verify Tax Commodity Codes

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

This script was created for a client who wanted to verify tax commodity codes in Dynamics GP against a spreadsheet. It uploads a tab delimited text file and outputs lines where the TCC on the file is different to the one in the database.

/*
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 #Items
	(
	ITEMNMBR VARCHAR(30)
	,ITEMDESC VARCHAR(100)
	,ITEMTYPE VARCHAR(100)
	,ITEMSHORTNAME VARCHAR(100)
	,ITEMGENERICDESC VARCHAR(100)
	,COUNTRYORIGIN VARCHAR(100)
	,TCC VARCHAR(100)
	)
GO

BULK INSERT
	#Items
FROM
	'C:\Temp\Items.txt'
WITH
	(FIELDTERMINATOR = '\t'
	,ROWTERMINATOR = '\n'
	,FIRSTROW = 2)
GO

SELECT
	['IV Item Master'].ITEMNMBR,*
FROM
	#Items AS ItemUpdate
LEFT JOIN
	IV00101 AS ['IV Item Master'] --Item Master (IV00101)
		ON
			ItemUpdate.ITEMNMBR = ['IV Item Master'].ITEMNMBR
WHERE
	['IV Item Master'].ITEMNMBR IS NULL
OR
	ItemUpdate.TCC <> ['IV Item Master'].TCC
ORDER BY
	ItemUpdate.ITEMNMBR
GO

DROP TABLE #Items
GO