Automate execution of multiple SQL Server 2012 scripts in specific order

Question

I needed some guidance with a task I have never worked with SQL Server 2012. Your help would be very much appreciated. I have inherited a data model in SQL Server.

Basically, I have 5 SQL scripts:

1. Script A
   2. Script B
   3. Script C
   4. Script D
   5. Script E

For running successfully script B,it needs access to tables generated by script A to perform calculation. Basically, the scripts are feeding each other. I need to run the scripts in a specific order.

My first idea was "stored procedure". So far, I have in the past only written a stored procedure to execute code from the same script that do not require executing other scripts.

My question is, what are some ideas you propose for automatically executing the above 5 scripts in a specific order? how can I do this? What would you recommend me to think when doing this?

Running the complete list of scripts takes around 10 hours.

Answer 1

You could easily create a new SQL Server Agent Job task like this:

1. Expand the SQL Server Agent node and right click the Jobs node in SQL Server Agent and select 'New Job'

2. In the 'New Job' window enter the name of the job and a description on the 'General' tab.

3. Select 'Steps' on the left hand side of the window and click 'New' at the bottom.

4. In the 'Steps' window enter a step name and select the database you want the query to run against.

5. Paste in the T-SQL command you want to run into the Command window and click 'OK'.

6. Click on the 'Schedule' menu on the left of the New Job window and enter the schedule information (e.g. daily and a time).

7. Click 'OK' - and that should be it.

Repeat these steps for each scripts in the order you want them to run. And, there you go!