SQL Job & Stored Procedure

A SQL Job is a scheduled task or process in Microsoft SQL Server that automates the execution of one or more SQL Server Agent jobs.

A Stored Procedure is a precompiled and stored collection of one or more SQL statements or queries. It can be called with a single command, providing a way to encapsulate and reuse code in a database.

In this scenario, we aim to periodically move data that is 2 months or older from main table to the backup table. We’ll store the SQL Query used for this task in a Stored Procedure and then execute it automatically everyday with an SQL Job.

Creating Stored Procedures

First, we create a stored procedure to copy the data older than 2 month to the backup table

Then the second stored procedure to delete the already copied data from the main table

Creating SQL Jobs

Now we create a Job to execute 2 procedures created above, we name the Job Daily_Backup_Conv

Then on first step, execute the first Stored Procedure

Then the second step to execute the second Stored Procedure

Here’s how the steps ended up

On schedule, select Daily Recurring at the desired time

To finsih things up, i like to log the operation onto Event Viewer everytime the job executes

Executing SQL Jobs

To immediately start the Job, right click on it and select Start Job

Then start from the first step

And looks like its a success

Right click on the Job and select History, we can see the execution history

We can also see the history on the Event Viewer

And checking on the tables, we can see the data has been successfully moved from the main table to the backup one