Insert Segments from Text File/CSV into Microsoft Dynamics GP

It seems that while I’ve posted about how to update segment descriptions from CSV in Microsoft Dynamics GP and how to copy them to a new company or even all companies, I’ve never actually posted the script I use to insert them.

I needed this script the other day and ended up using the update descriptions one as the basis for a new script to insert segments into Dynamics GP; this script will pick the text file (or CSV file if you change the second highlighted section to a comma), update any existing segments and insert new segments into the Segment Description Master (GL40200) table.

/*
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 #Segments
	(
		SGMTNUMB VARCHAR(100)
		,SGMNTID VARCHAR(100)
		,DSCRIPTN VARCHAR(100)
	)
GO

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

UPDATE
	Segments
SET
	Segments.SGMTNUMB = NewSegments.SGMTNUMB
	,Segments.SGMNTID = NewSegments.SGMNTID
	,Segments.DSCRIPTN = Left(NewSegments.DSCRIPTN, 31)
FROM
	GL40200 AS Segments
INNER JOIN
	#Segments AS NewSegments
		ON
			NewSegments.SGMTNUMB = Segments.SGMTNUMB
		AND
			NewSegments.SGMNTID = Segments.SGMNTID
GO

INSERT INTO GL40200
	(
		SGMTNUMB
		,SGMNTID
		,DSCRIPTN
	)
--VALUES
   (
		SELECT
			SGMTNUMB
			,SGMNTID
			,LEFT(DSCRIPTN, 31)
		FROM
			#Segments AS NewSegments
		WHERE
			(
				SELECT
					COUNT(*)
				FROM
					GL40200 AS Segments
				WHERE
					Segments.SGMTNUMB = NewSegments.SGMTNUMB
				AND
					Segments.SGMNTID = NewSegments.SGMNTID
			) = 0
	)
GO

DROP TABLE #Segments
GO

As always with a SQL script which makes changes, I’d make sure you have a good backup and test the script before running it in case you encounter problems.