Copy entire SQL table to another and truncate original table

Question

I am writing a stored procedure that will copy the entire contents of a table called "CS_Consolidation" into a backup table called "CS_ConsolidationBackup2016" all fields are exactly the same and the new data everyday must just be added after which the original table must be truncated.

I am however having a problem with my procedure and how it is written if anyone can help:

CREATE PROCEDURE BackUpData2
AS
BEGIN
SET NOCOUNT ON;

SELECT *
INTO [dbo].[CS_ConsolidationBackUp]
FROM [dbo].[CS_Consolidation]

TRUNCATE TABLE [dbo].[CS_Consolidation]
GO

Answer 1

If you are looking to create one backup table daily, would something like this work?

DECLARE @BackupTableName nvarchar(250)
SELECT @BackupTableName = 'CS_ConsolidationBackUp'  + CAST(CONVERT(date, getdate()) as varchar(250))
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @BackupTableName) 
BEGIN
    EXEC('DROP TABLE [' + @BackupTableName + ']')
END 
EXEC('SELECT * INTO [dbo].[' + @BackupTableName + '] FROM [dbo].[CS_Consolidation]')
TRUNCATE TABLE [dbo].[CS_Consolidation]