如何将 T-SQL 查询输出存储在新表中，其中包含执行查询的日期

Question

我必须创建一个查询，我必须每天运行该查询并在Excel中附加输出。

但我想要的是安排查询每天运行一次，输出将被存储，但我无法完全做到这一点。

例如，我有这个查询

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

结果如下所示：

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

现在，在安排它之后，我希望我的结果在执行查询时的每个新日期都进入新表中。

+----------------+----------+--------------+---------------+
| 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          |
+----------------+----------+--------------+---------------+

现在我想知道如何修改此查询以使其每天运行并将结果存储在包含当天日期的表中。

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

到目前为止，通过浏览，我知道我可以在SQL Server Agent中计划查询。

请帮助我

提前感谢！

Answer 1

如果您使用的是 SQL Server Standard 或 Enterprise，则可以使用 SQL Job 来计划任务。 如果使用的是速成版，则需要使用 SQL 作业的替代方法。

对于替代方法，可以使用PowerShell脚本来处理与SQL Server的连接并执行所需的命令。 更多关于它的信息在这里

PowerShell 脚本

$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

然后，需要保存 SQL 脚本，以便 PowerShell 可以执行它，每当完成时，它都会将结果写入日志文件。

在 SQL 脚本中，确保在文件的第一行指定数据库名称USE DatabaseName，以将脚本映射到目标 IT 数据库。 例：

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

此外，请确保 Windows 登录具有登录到数据库、执行、读取和写入的权限。(或者只是给它系统管理员角色(。否则，您将需要

这是一个通用脚本，您可以根据需要修改、扩展它。

现在，剩下的唯一事情就是在 Windows 中使用Task Scheduler来计划powershell脚本，以便它可以按照你提供的计划运行。

Answer 2

要安排查询，您可以点击下面的链接。

https://itknowledgeexchange.techtarget.com/itanswers/how-to-schedule-sql-query-to-run-in-microsoft-sql-server/

现在在添加一个像这样工作的 Tsql

if not exists(select * from sysobjects where name = 'tbl_exection_archive')
Begin
Select @DateTo as [Execution Date], [Capacity],[Utilization],([Utilization]/Capacity)*100 as [Utilization %] INTO tbl_exection_archive  
from Occupancy where 1=2
End
insert into tbl_exection_archive 
Select @DateTo as [Execution Date], [Capacity],[Utilization], 
([Utilization]/Capacity)*100 as [Utilization %] INTO tbl_exection_archive  
from Occupancy where  @DateTo > (select max([Execution Date]) from 
tbl_exection_archive)