SQLTeam.com | Weblogs | Forums

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

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!

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?

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

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