Implement Incremental logic on table which has no update column

Question

Hello All,

I have a table with following Columns(at the Server A on which i have only read access) in which GUID is a unique identifier or an identity column.

GUID
IsClientCharacteristic
PhysicalNoteType
StatusType
StatusReason
UnitOfMeasure

i need to implement incremental logic on this table and load changes to Server B ,but the issue is i don't have an update date on the source table to grab changes.

The size of the table is huge with 99 million rows so i dropped the option of doing lookup with SSIS,as it was taking long time.

Is there any way of Incremental loading.

But please know that i have only read access to the source table.

Any idea is really appreciated. Thanks.

Answer 1

1. You can try to use merge. Does it performs better than Lookup?
2. Another option is CDC (Change Data Capture)
3. The last option is to truncate the table, and re-load all data (using SSIS OLEDB Destination Fast Load)

Answer 2

Thanks for your reply Irusul.

1. As i don't have update to get the Delta transaction and then do Merge onto Destination table. If you mean to use merge is to use complete source table and then do merge to destination its performing same as Lookup eating up all my memory.

2. For CDC option i have only read access onto source server and moreover its a replicated server.

3. Currently i am stick to close to last option, what i am doing is truncating the table and reading split data(reading data from source and splitting into multiple tables on specific condition at least i am gaining read speed here) and then doing bulk insert from all these tables to the destination table.

I really appreciate your effort. Thanks a lot.

Answer 3

do you want to keep historical changes ? I've got a mad idea here but we need to try it out on reality incase of performance

1- dump the table into a staging area ( using SSIS ). keep always last version only (let's assume yesterday data)

2- apply ColumnStore Index on all columns to help out with performance here.

3- Dump today's data, apply Columnstore index as well. then do a left join where all columns equals. the changed records or added ones will remain to update to your destination keeping track of time if you want.

4- to get the deleted records reverse the left join equaling only the GUID.

The Key here is the ColumnStore index of SQL Server 2012, you can never do it without it. it'll seem like a lunatic action.

---

Wagdy Ishac www.sqldair.com

Answer 4

IF CDC is not an option then full load (truncate and insert) is only option

---

SQL Champ
Database Consultants NY