CODEWITHSUNDEEP
sql-serverstored-proceduressql-server-2014
JNR_Prog123
JNR_Prog123

Reputation: 133

Copy entire SQL table to another and truncate original table

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

Upvotes: 0

Views: 4485

Answers (3)

Sean Lange
Sean Lange

Reputation: 33581

Why do you want to copy the data and then delete the original? This is entirely more complicated and stressful to the system then you need. There is no need to create a second copy of the data so that you can just turn around and drop the first copy.

A much easier path would be to rename to current table and then create you new primary table. 

EXEC sp_rename 'CS_Consolidation', 'CS_ConsolidationBackUp';
GO
select * 
into CS_Consolidation
from CS_ConsolidationBackUp
where 1 = 0; --this ensures no rows but the entire structure is copied.

Upvotes: 2

DanielG
DanielG

Reputation: 1675

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]

Upvotes: 1

Ansonmus
Ansonmus

Reputation: 335

You are missing and "end" statement before "go". This is the correct code:

 CREATE PROCEDURE BackUpData2
 AS
 BEGIN
 SET NOCOUNT ON;

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

 TRUNCATE TABLE [dbo].[CS_Consolidation]
 end
 GO

Upvotes: 0

Related Questions