How to provide dynamic table name with Date Time in query of SQL Server

Question

I have to take backup of a table in SQL Server, but I don't want to give specific name to the particular backup table, because in a day there could be n number of backup of that particular table because of this reason, I have to change the name of backup table, whenever we take backup. I need a query in SQL Server so that it would append date/time to the name of backup table. Query should be like below:

Select *
into TableNameBackup_DateTime from TableName

I have tried by applying below query, but it is throwing an error.

Select * into 'TableNameBackup_'+GetDate() from TableName

Answer 1

It can be resolved by below query

   Declare @tablename AS nvarchar(80);
   SET @tablename='TableNameBackup_'+ 
   replace(replace(replace(convert(varchar(30),getdate(),121),'/','_'),':','_'),'.','_');
   EXECUTE('Select * into '+ @tablename+ ' from TableName');