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.
Solved! Go to Solution.
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.
@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)
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