SQL : Based on the DATE run the query and insert data into table and create stored procedure.

Question

HI All - Below is my current Query:

The query runs daily now and appends the data into the Table1 but the problem is if the query runs on the same day twice or thrice it should truncate the data loaded on the same day.

Lets say the query ran today at 8 AM and it inserted data into Table1 with the SnapShotDatetime of 03/22/2022 8: 00 AM

But if the user wants us to re-run the query again at 1pm, it should truncate the data loaded ONLY on same day ( 8 AM) and then run the query and load the latest data at 1 pm. Similarly if the query/job runs at 10 pm then it should the 1 pm data..

Could you please help on how to do this in the query and create the final stored procedure so i can set it up as a SQL agent job.
Thank you

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

If I understand correctly, you want the query to be able to clear data from earlier in the same day and insert the latest data every time you run it.

You can add a delete statement before inserting data to clear data from earlier in the same day.

Just like this:

Declare @Snapshot DATETIME = GETDATE()
SELECT column1, column2, column3, column4, column5, column6
	  ,SnapShotDatetime
FROM [USDW].[dbo].[Table1]
delete from [USDW].[dbo].[Table1] 
where convert(char(10),SnapShotDatetime,111) = convert(char(10),@Snapshot,111);
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')

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". 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.