SQL : Based on the DATE run the query and insert data into table and delete data greater than 12 months.

Question

Hi All - Below is my query which loads data into the table. This is the procedure which is scheduled to run once a day. Now the requirement is :

1. Check if there are any rows with todays date (based on the snapshot datetime) then do not load. If no rows then do the load.
2. Delete any rows where snapshotdate > 53 weeks. This means the table should have always only year (12 months of data). Could you please help me how to create this procedure.

Declare @Snapshot DATETIME = GETDATE()
SELECT column1, column2, column3, column4, column5, column6
	  ,SnapShotDatetime
  FROM [USDW].[dbo].[Table1]

  INSERT INTO [USDW].[dbo].[Table1] (column1, column2, column3, column4, column5, column6
	  ,SnapShotDatetime)
  Select column1, column2, column3, column4, column5, column6     
	  ,@Snapshot
	  from OPENQUERY ( Linkedserver, 'Select * from LinkedTable')

Answer 1

Hi @kkran Check this:

CREATE PROC SP_Temp
AS 
BEGIN
  DECLARE @Snapshot DATETIME = GETDATE()
  IF NOT EXISTS(SELECT * FROM [USDW].[dbo].[Table1] WHERE CONVERT(DATE,SnapShotDatetime)=CONVERT(DATE,@Snapshot))
  BEGIN 
    INSERT INTO [USDW].[dbo].[Table1] (column1, column2, column3, column4, column5, column6 ,SnapShotDatetime)
    SELECT column1, column2, column3, column4, column5, column6 ,@Snapshot
    FROM OPENQUERY ( Linkedserver, 'Select * from LinkedTable')
    WHERE CONVERT(DATE,SnapShotDatetime)=CONVERT(DATE,@Snapshot)
  END
  DELETE [USDW].[dbo].[Table1] WHERE CONVERT(DATE,SnapShotDatetime) < CONVERT(DATE, DATEADD(month, -12, GETDATE()))
END

EXEC SP_Temp

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.