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).