merge method

@override Future<bool> merge(List<Map<String, dynamic>> sourceData, { required List<String> matchOn, ConflictAction whenMatched = ConflictAction.update, ConflictAction whenNotMatched = ConflictAction.ignore, ConflictAction? whenNotMatchedBySource, List<String>? updateColumns, List<String>? insertColumns, Map<String, dynamic>? additionalValues, })

Implementation

@override
Future<bool> merge(
  List<Map<String, dynamic>> sourceData, {
  required List<String> matchOn,
  ConflictAction whenMatched = ConflictAction.update,
  ConflictAction whenNotMatched = ConflictAction.ignore,
  ConflictAction? whenNotMatchedBySource,
  List<String>? updateColumns,
  List<String>? insertColumns,
  Map<String, dynamic>? additionalValues,
}) async {
  if (sourceData.isEmpty) {
    throw InvalidArgumentException(
        'Source data cannot be empty for merge operation');
  }

  if (matchOn.isEmpty) {
    throw InvalidArgumentException(
        'Match columns cannot be empty for merge operation');
  }

  try {
    final conn = await getConnection();
    final paramBindings = <String, dynamic>{};

    final sourceColumns = sourceData.first.keys.toList();
    final valueGroups = <String>[];

    for (var row in sourceData) {
      final placeholders = sourceColumns.map((column) {
        final paramName = _nextParamName();
        paramBindings[paramName] = row[column];
        return ":$paramName";
      }).join(", ");
      valueGroups.add("($placeholders)");
    }

    final sourceClause =
        "(VALUES ${valueGroups.join(', ')}) AS source(${sourceColumns.join(', ')})";

    final matchConditions =
        matchOn.map((col) => "$getTable.$col = source.$col").join(' AND ');

    String mergeSQL =
        "MERGE INTO $getTable USING $sourceClause ON $matchConditions";

    if (whenMatched == ConflictAction.update) {
      final columnsToUpdate = updateColumns ??
          sourceColumns.where((col) => !matchOn.contains(col)).toList();
      final updateSets =
          columnsToUpdate.map((col) => "$col = source.$col").join(', ');
      mergeSQL += " WHEN MATCHED THEN UPDATE SET $updateSets";
    } else if (whenMatched == ConflictAction.delete) {
      mergeSQL += " WHEN MATCHED THEN DELETE";
    }

    if (whenNotMatched != ConflictAction.ignore) {
      final columnsToInsert = insertColumns ?? sourceColumns;
      final insertValues =
          columnsToInsert.map((col) => "source.$col").join(', ');
      mergeSQL +=
          " WHEN NOT MATCHED THEN INSERT (${columnsToInsert.join(', ')}) VALUES ($insertValues)";
    }

    if (whenNotMatchedBySource == ConflictAction.delete) {
      mergeSQL += " WHEN NOT MATCHED BY SOURCE THEN DELETE";
    }

    await conn.execute(mergeSQL, paramBindings);
    return true;
  } catch (e) {
    rethrow;
  }
}