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.
