Following the last post I did, on copying segments to a new company, I did some thinking and realised that with a little more work on the script I could make it even more useful when setting up clients with multiple companies and the same chart of account structure.
I have added a cursor to the script which selects all company databases from the DYNAMICS System Database (change the highlighted DYNAMICS if you’re using a named system database) and then loops though them doing the insert from the SourceDatabase.
As before, the script 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'
DECLARE
cursor_InterID Cursor
FOR
SELECT
INTERID
FROM
DYNAMICS..SY01500
INNER JOIN
master..sysdatabases
ON
name = INTERID
WHERE
INTERID <> @SourceCompany
Open cursor_InterID
FETCH NEXT FROM
cursor_InterID
INTO
@DestinationCompany
While (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @SQLStatement = 'INSERT INTO ' + @DestinationCompany + '..GL40200
(SGMTNUMB
,SGMNTID
,DSCRIPTN
,SEGCOUNT
,NOTEINDX)
(SELECT
SGMTNUMB
,SGMNTID
,Left(DSCRIPTN, 30)
,0
,0
FROM
' + @SourceCompany + '..GL40200 AS SD
WHERE (SELECT COUNT(GL.SGMNTID) FROM ' + @DestinationCompany + '..GL40200 GL
WHERE GL.SGMTNUMB = SD.SGMTNUMB AND GL.SGMNTID = SD.SGMNTID) = 0)'
EXEC (@SQLStatement)
FETCH NEXT FROM
cursor_InterID
INTO
@DestinationCompany
END
CLOSE cursor_InterID
DEALLOCATE cursor_InterID
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).
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.
3 thoughts on “Script To Copy Segments To All Companies”