Appending two tables in the database, one table updates once a day and another every hour

[Shivam]

Hi All,

I have to create a table (Table 3) from two tables.

Table 1 - Updates once a day and has previous day data

Table 2 - Updates every hour and has current day data.

       Example of Table 2 data - 

              Dec 4, 1 am update - It will have data between 12 am - 1 am, Dec 4

              Dec 4, 2 am update - It will have data between 12 am - 2 am, Dec 4 (Previous hour data may change hence including data starting 12 am)

              Dec 4, 3 am update - It will have data between 12 am - 3 am, Dec 4 and so on until 12 mid night

Table 3 = Table 1 (Dec 4) + Table 2 (Dec 5)

Is there a way to update table 3 where I can overwrite existing table 2 data every hour. If it was just 1 hour data every hour then I could have used append but since every hour has previous hours data, I'll have to overwrite.

My current solution  - Overwrite Table 3 every hour which will overwrite both Table 1 and Table 2. I am avoiding this solution because Table 1 has historic data and large to overwrite every hour.

I have tried my best to explain my scenario but if there is still anything confusing then please let me know. I'd highly appreciate any help.

Thanks.

[DanielBr]

Hi Shivam,

I might recommend to query Table 1 to only bring in the MOST RECENT data instead of overwriting the historical data every time.

You can do this by modifying the query in your input data tool with a datetime function. OR if you would prefer to build it visually in the canvas of designer you can use the In-DataBase Tools. 

[Shivam]

@DanielBr  Thank you for your reply. Hope I am able to understand your suggestion. If I bring the most recent data then I will miss changes done to the previous hour data.

I believe my problem comes down to this one question -

Is there a way to overwrite/replace only selected rows in the SQL Server database table? 

In my case I have to overwrite just current date rows in the table (one of the columns in the table is date column) 

[RolandSchubert · Accepted solution]

Hi @Shivam ,

the Output Data tool has the option to execute a "Pre Create SQL Statement". You could use this to delete the data for the current date (DELETE FROM table3 WHERE date = '2019-12-11'). Output Options should be "Append existing". This would delete rows for current date only and append the new data.

I think you will need to do this within a macro to adjust the date to "today". What do you think?

Best

Roland

[Shivam]

Hi @RolandSchubert,  It works perfectly. Thanks!

Shivam