How do you load batch data into the ‘sdb’ database through ADM/DSP without deleting the existing data from the table?

Answered

DSP User

• May 28, 2018 13:16

We have a requirement to extract data from a source system in batches from a huge volume table. I am using a customized query in Package to extract data. The first batch of data is successfully loaded into the ‘sdb’ database table using relevancy criteria. Now I need to extract the second batch of data using relevancy criteria. As we know, when we extract data from the Source system and load it into ‘sdb’ database, the Assemble-Package first deletes all existing data from the ‘sdb’ table and then loads fresh data.

Has anyone loaded batch data into the ‘sdb’ database through ADM/DSP without deleting the existing data from the table? Although we can do it on the SQL Server level by making a copy of the existing table with data, I need ADM/DSP process to load data into ‘sdb’ table in batches using relevancy criteria without deleting existing data.

1

• Best answer

  

  DSP Expert

  • May 28, 2018 15:02

  PCardoso: My approach to enable this through a robust Collect process is to create a replica of the table in question with a different name (e.g. TABLE_Delta) and build the Collect Process using this table instead, where [TABLE] is the actual table name you are looking to build a unified view of. Then, register a stored procedure as a rule for the TABLE_Delta entry. This stored procedure updates the table in question with the new information, performing whatever deletes might be required (or using a left join if applicable). To Alyssa's point, creating control columns could be useful in some use cases.

  0
• 

  DSP Expert

  • May 28, 2018 13:34

  ASliney: The truncate step is built into Collect (but not Assemble) and is not skippable. On the vertical view of the Assemble package, on the Advanced Properties tab, turn off Target Delete Records. Then you can run the Assemble package without emptying the table. I still recommend a backup/indicator of some kind on the already extracted records in case there is an error.

  0
• 

  DSP User

  • May 28, 2018 13:47

  Thanks Alyssa, I have already tried it. I have unchecked the 'Target Delete Records' before loading the first batch. The package deleted all existing data and loaded fresh data.

  0
• 

  DSP Expert

  • May 28, 2018 13:53

  ASliney: What version of DSP are you on? You're running the package directly from Assemble, correct? Did you try putting in a dummy Where clause for the delete e.g., MATNR = '0'? I tested this on a 6.6.4 box and it works as expected.

  0
• 

  DSP User

  • May 28, 2018 15:30

  Thank you for your guidance, Pedro. I did a similar kind of procedure on the database level. I replicated the table to another table name (Table_Delta) and every time the batch download finished, I inserted data from the main table to the delta table. Once all batches are completed, I will just rename the delta table to the main table.

  Do we have any functionality on ADM level with minimal manual work?

  0
• 

  DSP Expert

  • May 29, 2018 09:29

  VGazquez: Hi Syed, In addition to the previous answers, please check this post in Data Insiders, which details how to merge tables in Collect: https://support.syniti.com/hc/en-us/community/posts/360007976134--Collect-How-to-merge-tables

  This post might help you insert multiple batches into a single table. It also shows you how to use the Assemble Where clause (in case you need it). You can also uncheck the advance property in the Assemble package "Target Delete Records."

  0
• 

  DSP User

  • May 29, 2018 14:50

  I found this post on the BOA Support page to be very useful:

  https://support.syniti.com/hc/en-us/community/posts/360008039593-Download-Data-in-Batch-from-Huge-Volume-Source-Table

  0

Please sign in to leave a comment.