How do you create a job that saves a insert script to a shared location?

1

Here at my job I am looking to streamline a process that has been ongoing for 10 years. This is a monthly report that is created for another unit.

  1. We open a SQL query called "SQL File Builder". In this query, there are 2 parameters that we have to change for the report, Month and Year. Today is mid May 2019 so we would put the local values as:
    Reporting Year = 2019
    Reporting Month = 4 (we run the previous month)

  2. Once we execute the query, it results in a raw INSERT Script.

  3. We copy the INSERT Script and paste it into a new query window and save the query (with appropriate name: Report052019_UnitA) in a common shared location for the other unit to grab.

My question is, would a Job solution be appropriate to streamline this process? How do I approach it?

Thanks in advance!

2

what part of the script is static and what part is dynamic?
in other words how much of the script changes from one month to another?

3

The only thing I do is replacing the parameter value in the script for Month and Year.

4

You can automate calculation of the prior month's month number and year as follows:

Reporting Year = DATEPART(YEAR,DATEADD(MONTH,-1,GETDATE()));
Reporting Month = DATEPART(MONTH,DATEADD(MONTH,-1,GETDATE()));

As for the rest of it, it may be possible to automate that as well, but can't say without seeing the script.

1 Like