I have a bit of catching up to do on writing posts, but to start the year off I am back to some SQL scripts. As I have mentioned before I have a number of clients with multiple, and in some cases well over 100, companies in Microsoft Dynamics GP.
I do try to make life easier for them when possible so have been sharing a number of scripts I have for implementing systems. One such script is one which will copy financial (fiscal) calendars from one database to another.
There are three parameters at the top which need to be set before running the script:
- Year
- Source Company
- Destination Company
These parameters are highlighted below:
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://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).
*/
DECLARE @Year VARCHAR(4) = '2014'
DECLARE @SourceDatabase VARCHAR(5) = 'TWO'
DECLARE @DestinationDatabase VARCHAR(5) = 'THREE'
DECLARE @SQLStatement VARCHAR(1000)
SET @SQLStatement =
'INSERT INTO ' + @DestinationDatabase + '..SY40101
(YEAR1,FSTFSCDY,LSTFSCDY,NUMOFPER,HISTORYR)
--VALUES
(SELECT
YEAR1,FSTFSCDY,LSTFSCDY,NUMOFPER,HISTORYR
FROM
' + @SourceDatabase + '..SY40101
WHERE
(SELECT
COUNT(YEAR1)
FROM
' + @DestinationDatabase + '..SY40101
WHERE
YEAR1 = ' + @Year + ') = 0
AND
YEAR1 = ' + @Year + ')'
EXEC (@SQLStatement)
SET @SQLStatement =
'INSERT INTO ' + @DestinationDatabase + '..SY40100
(CLOSED,SERIES,ODESCTN,FORIGIN,PERIODID,PERIODDT,PERNAME
,PSERIES_1,PSERIES_2,PSERIES_3,PSERIES_4,PSERIES_5,PSERIES_6,
YEAR1,PERDENDT)
--VALUES
(SELECT
CLOSED,SERIES,ODESCTN,FORIGIN,PERIODID,PERIODDT,PERNAME
,PSERIES_1,PSERIES_2,PSERIES_3,PSERIES_4,PSERIES_5,PSERIES_6,
YEAR1,PERDENDT
FROM
' + @SourceDatabase + '..SY40100
WHERE
(SELECT
COUNT(YEAR1)
FROM
' + @DestinationDatabase + '..SY40100
WHERE
YEAR1 = ' + @Year + ') = 0
AND
YEAR1 = ' + @Year + ')'
EXEC (@SQLStatement)
GO
The calendar will only be copied if the destination database does not contain a calendar with the same name.