Hello Experts,
I need your thoughts on this please..
So I have a source table on Server A and the dest table on server B.
There are about more ~2 millions rows in source table. I will be using SSIS package.
I have been requested to load data weekly into the destination table. As this is too much data (for truncate and load) to do it weekly, preferred is to do it incrementally based on two key columns(say col1 and col2) and dateupdated
I know we have option to do the upsert into two ways
1)Merge Statement(T-SQL)
2)source-lookup-insert and update(staging table needed) (Very costly I Suppose ?)
But the issue is that source and destination tables are on two different servers. So the only option for me is option 2.
But was told it a very costly process because lookup..is that true?
So can anybody share if there is any another efficient process for incremental load involving two different servers?
Any suggestion/s is really appreciated.
Thanks