如何按日期时间生成表名?

2021-09-10 00:00:00 sql tsql sql-server-2008 sql-server

我意识到这在语法上很糟糕，但我认为它在某种程度上解释了我正在尝试做的事情.本质上，我有一个批处理作业，每天早上要在一个小表上运行，作为规范的一部分，我需要在每次加载之前创建一个可以通过报告访问的备份.

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

我怎样才能实现这个功能，或者我应该考虑用更好的方式来做这个吗?

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;

现在，您可能还想添加一些逻辑，使脚本在一天内运行多次时不会出错，例如

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

当您对逻辑感到满意并想要执行命令时，只需在 PRINT 和 EXEC 之间交换注释即可.

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

相关文章