Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

Shivam
8 - Asteroid

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.

 

 

4 REPLIES 4
DanielBr
Alteryx
Alteryx

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
8 - Asteroid

@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
16 - Nebula
16 - Nebula

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
8 - Asteroid

Hi @RolandSchubert,  It works perfectly. Thanks!

 

Shivam

Labels