Script To Copy Segments To A New Company

I have been doing some work with a client recently where we were creating a number of new companies which were to share the same chart of accounts (with only the first segment representing the company being different).

So we planned to use Integration Manager to to integrate a file containing the new chart of accounts. However, before we could load the accounts themselves we needed to get the Segments loaded.

Tis could be done by creating a SmartList in SmartList Designer to get the Segment Number, Segment ID and Segment Description and then use File Import to load the segments into the new company; or I could knock together a quick SQL script to do the job.

Obviously, as I am writing this post, I opted to go the route of writing an SQL Script. The highlighted pieces at the top are the Source and Destination Company databases; change these to the relevant databases.

The script also checks to make sure the Segments don’t already exist in the destination before doing the insert.

DECLARE @SourceCompany AS VARCHAR(5)
DECLARE @DestinationCompany AS VARCHAR(5)
DECLARE @SQLStatement AS VARCHAR(2000)

SET @SourceCompany = 'TWO'
SET @DestinationCompany = 'TWOA'

SET @SQLStatement = 'INSERT INTO ' + @DestinationCompany + '..GL40200
	(SGMTNUMB
	,SGMNTID
	,DSCRIPTN
	,SEGCOUNT
	,NOTEINDX)
	
	(SELECT
		SGMTNUMB
		,SGMNTID
		,Left(DSCRIPTN, 30)
		,0
		,0
	FROM
		' + @SourceCompany + '..GL40200 AS SM
	WHERE (SELECT COUNT(GL.SGMNTID) FROM ' + @DestinationCompany + '..GL40200 GL
		WHERE GL.SGMTNUMB = SM.SGMTNUMB AND GL.SGMNTID = SM.SGMNTID) = 0)'

EXEC (@SQLStatement)
GO

If you run this script, please be careful and ensure you have a good backup before running the script (as I don’t supply a warranty with any script I post here; that said I am happy to talk to people if they have questions or would like the script extending).