Set-based operations are used to perform bulk operations more efficiently using record set operations. Data entity must Support set-based SQL operation.
Set-based operations cannot be used on entities using the following -
a. Views
b. Unions
c. Time-State Enabled
d. Temporary or TempDB
e. Mixed SaveDataPerCompany
f. Fields in AK(Alternate Key) not mapped in entity
g. Aggregations
h. Composite Data Sources
i. Joins other than inner and outer join
Now if you want to turn on the set based operation but want to run the data methods or perform some operations while data is being imported with the help of data entity, then have a look at the below code
public static container copyCustomStagingToTarget(DMFDefinitionGroupExecution _dmfDefinitionGroupExecution)
{
MCOEmployeeDataTableStaging staging;
MCOEmployeeDataTable EmployeeDataExisting;
MCOEmployeeDataTable EmployeeDataInserted;
MCOEmployeeDataTable EmployeeDataUpdated;
Int64 insertedCount, updatedCount;
boolean successful;
MCOEmployeeDataTableEntity::resetTransferStatus(_dmfDefinitionGroupExecution);
try
{
ttsbegin;
// Validate
//LogisticsAddressCityEntity::validateStaging(_dmfDefinitionGroupExecution);
EmployeeDataUpdated.skipDataMethods(false);
// Update records
update_recordset EmployeeDataUpdated
setting FirstName = staging.FirstName,
LastName = staging.LastName,
PersonnelNumber = staging.PersonnelNumber,
EmploymentStartDate = staging.EmploymentStartDate,
EmploymentEndDate = staging.EmploymentEndDate,
Position = staging.Position,
PositionDescription = staging.PositionDescription,
AssignmentStartDate = staging.AssignmentStartDate,
Email = staging.Email,
Status = staging.Status,
Log = staging.Log,
AddressBook = staging.AddressBook,
PayrollCompanyCode = staging.PayrollCompanyCode,
PositionStatus = staging.PositionStatus,
RehireDate = staging.RehireDate
join staging
where staging.PersonnelNumber == EmployeeDataUpdated.PersonnelNumber
&& staging.DefinitionGroup == _dmfDefinitionGroupExecution.DefinitionGroup
&& staging.ExecutionId == _dmfDefinitionGroupExecution.ExecutionId
&& staging.TransferStatus== DMFTransferStatus::NotStarted;
if (EmployeeDataUpdated.RowCount() > 0)
{
// Only add records updated instead of -1 when no records returned.
updatedCount = EmployeeDataUpdated.RowCount();
}
// Insert the staging records into the physical tables.
EmployeeDataInserted.skipDataMethods(true);
insert_recordset EmployeeDataInserted (FirstName,LastName,PersonnelNumber,EmploymentStartDate,EmploymentEndDate,Position,PositionDescription,AssignmentStartDate,Email,Status,Log,AddressBook,PayrollCompanyCode,PositionStatus,RehireDate)
select FirstName,LastName,PersonnelNumber,EmploymentStartDate,EmploymentEndDate,Position,PositionDescription,AssignmentStartDate,Email,Status,Log,AddressBook,PayrollCompanyCode,PositionStatus,RehireDate from staging
where staging.DefinitionGroup == _dmfDefinitionGroupExecution.DefinitionGroup
&& staging.ExecutionId == _dmfDefinitionGroupExecution.ExecutionId
&& staging.TransferStatus == DMFTransferStatus::NotStarted
notexists join EmployeeDataExisting
where EmployeeDataExisting.PersonnelNumber == staging.PersonnelNumber
&& EmployeeDataExisting.FirstName == staging.FirstName
&& EmployeeDataExisting.LastName == staging.LastName;
if (EmployeeDataInserted.RowCount() > 0)
{
// Only add records inserted instead of -1 when no records returned.
insertedCount = EmployeeDataInserted.RowCount();
}
// Update the staging table to specify records were successfully processed.
update_recordset staging
setting TransferStatus = DMFTransferStatus::Completed
where staging.DefinitionGroup == _dmfDefinitionGroupExecution.DefinitionGroup
&& staging.ExecutionId == _dmfDefinitionGroupExecution.ExecutionId
&& staging.TransferStatus == DMFTransferStatus::NotStarted;
successful = true;
ttscommit;
}
finally
{
if (!successful)
{
// An error occurred, move all rows to error state. Note this is done in a finally
// block instead of a catch block so that the exception will automatically propegate
// to the calling code.
update_recordset staging
setting TransferStatus = DMFTransferStatus::Error
where staging.DefinitionGroup == _dmfDefinitionGroupExecution.DefinitionGroup
&& staging.ExecutionId == _dmfDefinitionGroupExecution.ExecutionId
&& staging.TransferStatus == DMFTransferStatus::NotStarted;
}
}
return [insertedCount, updatedCount];
}
private static void resetTransferStatus(DMFDefinitionGroupExecution _dmfDefinitionGroupExecution)
{
MCOEmployeeDataTableStaging staging;
update_recordset staging
setting TransferStatus = DMFTransferStatus::NotStarted
where staging.DefinitionGroup == _dmfDefinitionGroupExecution.DefinitionGroup
&& staging.ExecutionId == _dmfDefinitionGroupExecution.ExecutionId
&& staging.TransferStatus != DMFTransferStatus::NotStarted
&& staging.TransferStatus != DMFTransferStatus::Completed;
}