Insert and update the records to sqlserver

[jasper]

Kumar_s,

I'm not doubting the use of change capture, BUT:
easy setup using change capture would be: extract target(before), extract source(after), go trouch change capture, apply changes.

From my oracle experience(so again not sure if relevant on sql-server) even if you have exactly the changes it is faster to truncate and reload then to apply more then 10% inserts/updates(mainly the updates offcource).
So if you add the extract to this(before image) it only gets worse(maybe not much, but worse none the less).

[jdmiceli]

A completely other scenario: If the number of inserts/updates is large and it's difficult to find the changed records: Just truncate and reload.
I'm not used to sql server but on an oracle this is the fastest solution once you reach about 10% inserts/updates.

Hi all! My first response on this site. The problem with what Jasper is suggesting is that if the SQL Server table has a foreign key on it of some sort (referential integrity being enforced between tables) and there is a child row depending on rows in this table, then truncate won't work. Delete might, depending on your RI, but it would be very slow on a large table. Hope that helps!

Bestest,
jdmiceli

[ray.wurlod]

Welcome aboard. :D

Thanks for the illumination. The same is true if the database is Oracle and, I suspect, most others. For the DBA Red Brick offers a singularly useful OVERRIDE REFCHECK clause but in other databases you need to remove the FOREIGN KEY constraints to be able to truncate the table.