Insert Segments from Text File/CSV into Microsoft Dynamics GP

Microsoft Dynamics GPIt 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 a Dynamics GP Consultant (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.