How to SELECT * into a SQL table incremntally by date?

Question

I have a SQL Server table called "tblProducts".

Sometimes I backup this table by making a copy of it with this simple query:

SELECT *
INTO [test01].[dbo].[tblProducts_20141206]
FROM [test01].[dbo].[tblProducts]

Every time when making a backup, the date is included in the table name.

I would like to create a SQL Job that runs this kind of query once every week.

Is it possible to maybe in a stored procedure or declaring a variable to achieve this that allows the backed-up table name to be named like [tblProducts_todaysDate]?

Thanks.

Answer 1

You need Dynamic SQL to create the tables names appended with date.

CREATE PROC usp_createtable( @tablename VARCHAR(20), 
                 @Dbname    VARCHAR(20), 
                 @SchemaName VARCHAR(20)) 
AS 
  BEGIN 
    DECLARE @sql NVARCHAR(max) 
    SET @sql =' SELECT * INTO '+@Dbname+'.'+@SchemaName+'.'+@tablename+'CONVERT(VARCHAR(8), GETDATE(), 112) FROM '+@Dbname+'.'+@SchemaName+'.'+@tablename'' 
    EXEC sp_executesql 
      @sql 
  END

Answer 2

If you are using a SP, you can do something like:

 CREATE PROC sp_createATable
 @name        VARCHAR(20) AS
 CREATE TABLE @name
 ...
 do your insert

Or, if you want to, w/o SP:

 DECLARE @name varchar(20)
 SET @name = 'tblName' + SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]
 CREATE TABLE @name
 ...
 do your insert