Unlike Oracle loader or BCP All our ETL tools are capable of updating data in the database based on update key.
For the example provided below, the following SQL will be executed
(Update key is CustomerId,OrderNo)
Add New And Update Old Records
Select count(*)
from [DEMO].[dbo].[orders]
where CustomerId=? And OrderNo=?
If any records found they will be updated by executing
Update [DEMO].[dbo].[orders]
set orderdate=?,
amount=?
where customerid=? And OrderNo=?
If no records found they will be added
Update Records
Update [DEMO].[dbo].[orders]
set OrderDate=?,
Amount=?
where CustomerId=? And OrderNo=?
Delete Records
Delete from [DEMO].[dbo].[orders]
Where CustomerId=? And OrderNo=?
Note:
- Add New And Update Old Records is not supported for SQL Server Connection use ODBC connection instead.
- If you are updating large table you would be better off if temporary table is used
