How to create a backup table from a table with current date & time in sql server.

The spaces in date & time should be replaced by underscore.

For Backup I am doing this :-

select * into BKP_TABLE_STUDENT
from TABLE_STUDENT

And for fetching DateTime I am using this :-

select convert(varchar, getdate(), 0)

Above gives this format -> Mar 2 2022 4:02PM

So, I need to combine the above datetime and the table name.

E.g. Table name will be BKP_TABLE_STUDENT_Mar_2_2022_4_02PM

CodePudding user response：

You can conactenate your table name with a formatted string.
I would prefer a 24 hour clock than AM / PM but it's your call

SELECT CONCAT('BKP_TABLE_STUDENT_',FORMAT(getdate(),'MMM_dd_yyyy_hh_mm_tt')) as backup_name

returns

BKP_TABLE_STUDENT_Mar_02_2022_11_24_AM