SQL query to replace current data with condition

Transact-SQL
1

Hello Guru's,

Query is that " We have two tables in sql, an Everyday backup will run from 'Temp_Data' table to 'Final_Data' table. Backup replaces the current data with previous data.

We need one set of backup to retain when Month gets changed(Apr to May) (or) when Forecast gets changed(Forecast06 to Forecast07)."

Below is the code, I tried. We are getting issues with retaining backup when Forecast gets changed. It is currently overwriting the Previous data. Backup retaining working only when Month gets changed.

Declare @cnt Int
Select @cnt = Count(*) From Final_Data Where Scenario In
(select distinct scenario from TEMP_Data where scenario like 'Forecast%')
And DatePart(MM,DateTime) = DatePart(MM, GETDATE()) AND
DATEPART(YYYY,DATETIME)=DatePart(YYYY, GETDATE())

IF (@cnt >0)
Begin
delete from Final_Data Where
datetime = (
Select DISTINCT dateTime From Final_Data Where Scenario =
(select distinct scenario from TEMP_Data where scenario like 'Forecast%')
and
DatePart(MM,DateTime) = DatePart(MM, GETDATE()) AND
DATEPART(YYYY,DATETIME)=DatePart(YYYY, GETDATE()))
End

Regards
Sunil

2

Please provide sample data in the form of create table statement(s) and insert statement(s). Generally if you want to keep the previous data create a history table and add an update delete trigger to capture values changed or deleted.