upsertFailExample.kt

// Table definition
object Cities : Table() {
    val id = integer("id").autoIncrement().primaryKey()
    val name = varchar("name", 50)
    val create_date = datetime(name = "create_date")
}
// Entity definition
data class City(
  val id: Int,
  val name: String
)

fun execute(cities: List<City>) {
  transaction {
    Cities.batchInsertOnDuplicateKeyUpdate( cities, listOf(id, name) ) { batch, city ->
      batch[Cities.id] = city.id
      batch[Cities.name] = city.name
    }
  }
}

// The below code is just a copy-paste that should actually be in the lib
class BatchInsertUpdateOnDuplicate(table: Table, val onDupUpdate: List<Column<*>>) : BatchInsertStatement(table, false) {
  override fun prepareSQL(transaction: Transaction): String {
    val onUpdateSQL = if (onDupUpdate.isNotEmpty()) {
      " ON DUPLICATE KEY UPDATE " + onDupUpdate.joinToString { "${transaction.identity(it)}=VALUES(${transaction.identity(it)})" }
    } else ""
    return super.prepareSQL(transaction) + onUpdateSQL
  }
}

fun <T : Table, E> T.batchInsertOnDuplicateKeyUpdate(data: List<E>, onDupUpdateColumns: List<Column<*>>, body: T.(BatchInsertUpdateOnDuplicate, E) -> Unit) {
  data.
      takeIf { it.isNotEmpty() }?.
      let {
        val insert = BatchInsertUpdateOnDuplicate(this, onDupUpdateColumns)
        data.forEach {
          insert.addBatch()
          body(insert, it)
        }
        TransactionManager.current().exec(insert)
      }
}