Please also have a look in our OnlineHelp for further information.
In a Delta Extraction with the Xtract IS DeltaQ Component it is possible to download records that are earlier transfered with a full-update. Since the release of SQL Server 2008 you can use the command called Merge at this point to perform Insert and Update commands easily in one step. In our next example we want to update an updated Customer (TA VD02), which is present in our Database, with the Merge command. We can also use this package to insert a new customer in our table in our Database.
In this sample we work with 2 tables. The first one (Raw) is going to be used to store the values coming from SAP, either to update them later in the next table (Staging) with the merge command, or, if the record is not present, to insert them.
In the first Screenshot we see the whole Control Flow. First we need a “Execute SQL Task”. We rename the task to “Empty Raw Table”
Delete the records in the Raw-Table to be ready for the incoming Data.
The first step is to get the Data out of SAP. Insert a Data-Flow-Task in the Control Flow, as you can see in the Screenshot, and rename it in “Get Delta from Customer”. In this task the extraction from SAP is handled by Xtract IS DeltaQ. The changed or new records are inserted in the Raw-Table.
The third and last “Execute SQL Task” contains the merge command. We insert the Data for the connection.
and then the following Merge command in the field SQLStatement.
Merge Staging
using Raw on Staging.KundenNo = Raw.KUNNR
when matched then update
set Staging.Name = Raw.Name1,
Staging.City = Raw.ORT01
when not matched then insert
(KundenNo,Name,City)
values(KUNNR,NAME1,ORT01);
In SAP we change a record in the transaction VD02. As we can see on the Image, we append two X and save the customer.
In our Staging-Table we have exactly 7706 Records and our customer with the number 0000001172 has no X in this name.
We execute the package and see the changed record in the DataViewer.
As we can see in our Staging-Table the count of records has not been changed, only the name is changed.
