Question from a beginner - Compare one to all across a database

[JohnMaty]

I have two databases (A and B).  Database A, I control and from which I get a datetime called "heartbeat".  Database B is owned by someone else and I can only read it.  As a result, I need to scan a table from Database B and see if the Create Date or LastUpdate Date for each row is greater than or equal to the datetime ("heartbeat") stored in Database A.  I need to create and/or update a table in Databse A that is identical to the table from Database B.  I need to reset the heartbeat in Database A to the same time the table scan started so it is available for the next run.

In other words I need to replicate Database B into Database A. 

I can't figure out how to get the "heartbeat" to compare to Database B's create or lastupdate date.  I tried using JOIN but that seems to require the fields to match exactly.  Any help would be greatly appreciated.

[jdunkerley79 · Accepted solution]

So some rough ideas to get you started.

You want to use and Append Fields tool to add the heartbeat value from Database A to the data from Database B

You can then use a filter tool to pick out the rows where update/create is greater than the heartbeat

You can then update database with these records.

Finally you can use a summarise tool to calculate the new heartbeat value and update that in Database A

Hope that gives you enough to get started

[JohnMaty]

That worked better than could be expected.  Thank you!

[JohnMaty]

I am still having a bit of trouble with the datetime.  When do I incorporate the date?  I need to know exactly when the update process kicks off and when it finishes, that way when the update process kicks off subsequently, the date used will be from when the last job started, not ended.  Will I need to store these dates in a table?

[jdunkerley79]

I think it is enough to take the heartbeat you started with and store this into the second server as you will have processed all messages before it already.

Alternatively I would set the new heartbeat as the maximum value of the LastUpdate of the read in rows.

I am not in front of Alteryx at the moment but happy to put together a sample of what I mean if that would help.

[JohnMaty]

That would be great!  Please see the workflow I have created already.

[jdunkerley79]

Please see attached adjusted macro.

This get the maximum last update dt and saves to a test_heartbeat table.

This is then read in as an additional input to the filter the input data.

It returns data greater than the maximum of heartbeat time from the two different heart beat tables.

Hopefully this will give you a hint of how to proceed.

[JohnMaty]

I need to expand upon this question I already marked as answered.  I am having an issue with how this process is working.  It appears as though this solution has to scan the entire table in order to make the append and filter processes.  A few of the tables i use are huge.  How can I take the append process and apply it to the dbo.Matter table?  I know the Matter table is indexed on LastUpdateDate.  I want to take the LasteUpdateDate from this table and pull in any records that are >='heartbeat_time'.  Can I do this as a pre/post SQL statement within dbo.Matter?

THis table alone is about 1GB and take about 10 minutes to process if I do a full table scan.

[JohnJPS]

Could you use a Dynamic Input and feed in the heartbeat_time to use as a modifier to the SQL used to pull from the larger dbo.Matter table?

[JohnMaty]

I have never used a dynamic input tool but I could definately give it a try.  Just now have to figure out how to use it.