CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Michael A
Michael A

Reputation: 9900

How to generate table name by datetime?

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?

Upvotes: 1

Views: 12560

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280383

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

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

Upvotes: 5

aked
aked

Reputation: 5815

I am not sure if you are looking for this.

DECLARE @date datetime, @CMD varchar(1000);
SET @date = CONVERT(VARCHAR(10), GETDATE(), 105)
SET @cmd = 'SELECT * INTO [dbo.T_TABLE_Backup_' + replace(cast(@date as varchar(11)),' ','_')+'] FROM dbo.T_TABLE'
print @cmd;

Upvotes: 0

Related Questions