SQL backup to a dynamic-named table

This script is an example of how a table can be created with a dynamic name such as a have a datestamp as part of the table name. Then data from a live table is inserted into the new table. This is a good script for auto creating a backup of a within a script that may be modifying or deleting data.

DECLARE @sqlSalesOrders NVARCHAR(max)
DECLARE @Date Varchar(8)

-- creates current date as a varchar (ie '20200806')
SET @Date =  CONVERT(VARCHAR(8), GETDATE(), 112)

-- Creates dynamic SQL statement using date variable    
SET @sqlSalesOrders ='SELECT * INTO '+ 'BackUpSalesOrder_'+@Date +' FROM ' + 'SalesOrder'

--Executes the dynamic SQL statements
EXEC sp_executesql @sqlSalesOrders


Back to Home