Deleting All Data From Staging Table Before Running Transform Map

[DebjitGhosh31]

I have a transform map which is taking data from source table (Table A) and populating Target (Table B). Now I need to delete all existing records from Table B every time before the transform map runs. As I want table B to have exact same data what's there in table A (staging). I created an OnStart Transform script as follows - 

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

// Add your code here
var gdt1 = new GlideRecord('<Table B>');
gdt1.deleteMultiple();
})(source, map, log, target);

But the problem is if there are 100 records in Table A then this script is running 100 times and at the end I have only 1 record in Table B.

I know I am missing something very obvious,  please help.

Regards,

Debjit

[shyamkumar VK]

Basically we have option to delete duplicate using Colease and before transform script

Why your trying to delete data from target table instead why cant you update the records? what is your exact requirement

[DebjitGhosh31]

Hi,

Thanks for the response.

The requirement is that the target table on any given day will be a exact replica of what's there in the data source. Hence deleting all records and posting everything that comes from source.

[jaheerhattiwale]

@DebjitGhosh31 Create a onBefore transform script and add below code to it

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

var sourceTable = new GlideRecord(source.getTableName());
sourceTable.addQuery("sys_import_set=" + source.sys_import_set.toString());
sourceTable.addQuery("sys_import_state=inserted");
sourceTable.setLimit(1);
sourceTable.query();

if (sourceTable.next()) {
ignore = true;
} else {
var targetTable = new GlideRecord(target.getTableName());
targetTable.deleteMultiple();
}

})(source, map, log, target);

Please mark as correct answer if this solves your issue.

[jaheerhattiwale]

@DebjitGhosh31 Try this, it should work.