How to store T-SQL query output in new table with date of that day when query executed

I have to created a query which i have to run daily and attach the output in Excel.

But what I want is to schedule the query to run on a daily basis and the output will get stored but I’m not able to do it completely.

For example I have this query

Select 
    @DateTo as [Execution Date], [Capacity],[Utilization],
    ([Utilization] / Capacity)*100 as [Utilization %] 
From 
    Occupancy

Result looks like this:

+----------------+----------+--------------+---------------+
| Execution Date | Capacity | Utilization  | Utilization % |
+----------------+----------+--------------+---------------+
| 25-08-18       |    10000 |         9000 |            90 |
+----------------+----------+--------------+---------------+

Now after scheduling it, I want my result to go in new table with every new date when query is executed.

+----------------+----------+--------------+---------------+
| Execution Date | Capacity | Utilization  | Utilization % |
+----------------+----------+--------------+---------------+
| 25-08-18       |    10000 |         9000 | 90            |
| 26-08-18       |    10000 |         8000 | 80            |
| 27-08-18       |    10000 |         5000 | 50            |
| 28-08-18       |    10000 |         9230 | 92.3          |
+----------------+----------+--------------+---------------+

Now I want to know how I can modify this query to make it run daily and store results in a table with the date of that day.

Select 
    @DateTo as [Execution Date], [Capacity], [Utilization],
    ([Utilization]/Capacity)*100 as [Utilization %]
From 
    Occupancy

So far with browsing I get to know that I can schedule the query in SQL Server Agent.

Please help me

Thanks in advance!

Answer

If you’re using SQL Server Standard or Enterprise, you could use SQL Job to schedule a task. if you’re using express edition, you’ll need to use an alternative method of SQL Job.

for an alternative, you could use PowerShell script to handle the connection to SQL Server and executing the commands you want. more about it here

PowerShell Script

$SqlFile     = "XSQLJob.sql"               # Replace X with File Path 
$OutputFile  = "XSQLJob_Log.txt"           # Replace X with File Path 


## SET Connection
Set-Location "SQLSERVER:SQLX"             # Replace X with ServerNameInstanceName

## Do SQL Query
Invoke-Sqlcmd -InputFile $SqlFile | Out-File -FilePath $OutputFile

Then, you need to save an SQL script so the PowerShell can execute it, and whenever its done, it’ll write the results to a log file.

inside the SQL script, ensure that you specify the database name USE DatabaseName at the first line of file to map the script to the target it database. Example :

USE DatabaseName 

INSERT INTO SomeTable(ExecutionDate, Capacity, Utilization)
SELECT  
    CAST(GETDATE() AS DATE)
,   [Capacity]
,   ([Utilization] / Capacity)*100
From 
    Occupancy

Also, ensure that the windows login has the permissions to login to the database, execute, read and write. (or just give it sysadmin role). Otherwise, you’ll need to

This is a general use script, you can modify it, expand it as you need.

Now, the only thing left is to use Task Scheduler in Windows, to schedule the powershell script so it can run on the schedule that you provide.