Copy all tables in database and add new data without affecting the old data SQL

Question

Can anyone help me with a stored procedure that would allow all the tables in a database to be copied and the original tables must be truncated at the end, and when the procedure runs everyday all new data must be copied from the original table to the backup tables without affecting the existing data.

This is what I've done

CREATE PROCEDURE ZS_Backup
AS
BEGIN

SELECT * INTO
 [dbo].[CS_ArrivalsBackUp], 
 [dbo].[CS_awbBackUp],
 [dbo].[CS_awb_arrivalsBackUp],
 [dbo].[CS_awb_dlvBackUp],
 [dbo].[CS_awb_iataBackUp],
 [dbo].[CS_awb_iata_arrivalsBackUp],
 [dbo].[CS_ConsolidationBackUp],
 [dbo].[CS_part_agents],
 [dbo].[CS_part_consignee],
 [dbo].[Evo_Customers],
 [dbo].[Evo_Products],
 [dbo].[int_log_arrivals],
 [dbo].[int_log_deliveries],
 [dbo].[integration_logs],
 [dbo].[period],
 [dbo].[public_holidays],
 [dbo].[rates],
 [dbo].[temp_Rates]

FROM [dbo].[CS_Arrivals],
       [dbo].[CS_awb],
      [dbo].[CS_awb_arrivals],
      [dbo].[CS_awb_dlv],
      [dbo].[CS_awb_iata],
      [dbo].[CS_awb_iata_arrivals],
      [dbo].[CS_Consolidation],
      [dbo].[CS_part_agents],
      [dbo].[CS_part_consignee],
      [dbo].[Evo_Customers],
      [dbo].[Evo_Products],
      [dbo].[int_log_arrivals],
      [dbo].[int_log_deliveries],
      [dbo].[integration_logs],
      [dbo].[period],
      [dbo].[public_holidays],
      [dbo].[rates],
      [dbo].[temp_Rates]

TRUNCATE TABLE [dbo].[CS_Arrivals],
           [dbo].[CS_awb],
           [dbo].[CS_awb_arrivals],
           [dbo].[CS_awb_dlv],
           [dbo].[CS_awb_iata],
           [dbo].[CS_awb_iata_arrivals],
           [dbo].[CS_Consolidation],
           [dbo].[CS_part_agents],
           [dbo].[CS_part_consignee],
           [dbo].[Evo_Customers],
           [dbo].[Evo_Products],
           [dbo].[int_log_arrivals],
           [dbo].[int_log_deliveries],
           [dbo].[integration_logs],
           [dbo].[period],
           [dbo].[public_holidays],
           [dbo].[rates],
           [dbo].[temp_Rates]

           END
           GO

Answer 1

Here you go,

be careful where you run this. For your safety I have commented out the bit that actually does the copy and truncate but instead I have set it so it would show you what would be executed:

DECLARE @table_name nvarchar(1000)
DECLARE @SQL nvarchar(1000)

DECLARE CUR CURSOR 
FOR 
SELECT name 
FROM sys.tables
WHERE type = 'U'
and name IN ('CS_ArrivalsBackUp', 
'CS_awbBackUp',
'CS_awb_arrivalsBackUp',
'CS_awb_dlvBackUp',
'CS_awb_iataBackUp',
'CS_awb_iata_arrivalsBackUp',
'CS_ConsolidationBackUp',
'CS_part_agents',
'CS_part_consignee',
'Evo_Customers',
'Evo_Products',
'int_log_arrivals',
'int_log_deliveries',
'integration_logs',
'period',
'public_holidays',
'rates',
'temp_Rates')
ORDER BY NAME

OPEN CUR

FETCH NEXT FROM CUR
INTO @table_name

WHILE @@FETCH_STATUS = 0  
BEGIN  

PRINT 'SELECT * INTO TMP_' + @table_name + ' FROM ' + @table_name
PRINT 'TRUNCATE TABLE ' + @table_name 

--SET @SQL = 'SELECT * INTO TMP_' + @table_name + ' FROM ' + @table_name
--EXEC (@SQL)

--SET @SQL = 'TRUNCATE TABLE ' + @table_name 
--EXEC (@SQL)

FETCH NEXT FROM CUR INTO @table_name  

END
CLOSE CUR
DEALLOCATE CUR