問題描述

我意識到這在語法上很糟糕，但我認為它在某種程度上解釋了我正在嘗試做的事情.本質(zhì)上，我有一個批處理作業(yè)，每天早上要在一個小表上運行，作為規(guī)范的一部分，我需要在每次加載之前創(chuàng)建一個可以通過報告訪問的備份.

I realize this is syntactically bad but I figure it somewhat explains what I'm trying to do. Essentially, I have a batch job that is going to run each morning on a small table and as a part of the spec I need to create a backup prior to each load that can be accessed by a report.

到目前為止我所擁有的是:

What I have so far is:

select  *
into    report_temp.MSK_Traffic_Backup_ + getdate()
from    property.door_traffic

我怎樣才能實現(xiàn)這個功能，或者我應該考慮用更好的方式來做這個嗎?

How can I make this function or should I consider doing this a better way?

推薦答案

DECLARE @d CHAR(10) = CONVERT(CHAR(8), GETDATE(), 112);

DECLARE @sql NVARCHAR(MAX) = N'select  *
into    report_temp.MSK_Traffic_Backup_' + @d + '
from    property.door_traffic;';

PRINT @sql;
--EXEC sys.sp_executesql @sql;

現(xiàn)在，您可能還想添加一些邏輯，使腳本在一天內(nèi)運行多次時不會出錯，例如

Now, you might also want to add some logic to make the script immune to error if run more than once in a given day, e.g.

DECLARE @d CHAR(10) = CONVERT(CHAR(8), GETDATE(), 112);

IF OBJECT_ID('report_temp.MSK_Traffic_Backup_' + @d) IS NULL
BEGIN
  DECLARE @sql NVARCHAR(MAX) = N'select  *
  into    report_temp.MSK_Traffic_Backup_' + @d + '
  from    property.door_traffic;';

  PRINT @sql;
  --EXEC sys.sp_executesql @sql;
END

當您對邏輯感到滿意并想要執(zhí)行命令時，只需在 PRINT 和 EXEC 之間交換注釋即可.

When you're happy with the logic and want to execute the command, just swap the comments between PRINT and EXEC.

這篇關(guān)于如何按日期時間生成表名?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！