Best method to mirror a large database table everyday

Question

Hi all,

I need to schedule a daily workflow that get data from one large table and mirror this data into another table. The table has a primary key. The workflow is very simple right now, something like this:

For this, as far as i know, i can use the following Output Options:

1)Delete Data & Append

2)Update; Insert If New

Some colleagues said to me that the 2 option is better because it uses less resources from the SQL Server. The problem is that sometimes an ID can be deleted from the Source and it need to be deleted on the Copy of the source too. And the 2 option does not do that by default.

What is the best way to proceed here? 1, 2, or even another option? Maybe using pre/post sql statements? Thanks!

MatthewO · Answer

@Inactive User the answer to this could be specific to the technology you are leveraging and I would recommend discussing it with your RDBMS provider. They will likely have guidance for running large copy jobs, potentially using change data capture mechanisms. From the Alteryx perspective, you can execute a stored procedure, or other pre/post SQL statements using input tools. It would likely be a simple workflow that could then be scheduled to execute the job daily.

unknown · Answer

.

unknown · Answer

Hi @MatthewO

Yes, this is a good idea indeed. But let me be more specific, my doubt is more related with what is the best way to write/read of of data inside of the database when i need to mirror the data everyday. I have this same situation of replicating data from API/flat files to DB too.