ktorm-support-sqlite/org.ktorm.support.sqlite/bulkInsertOrUpdate

bulkInsertOrUpdate

fun <T : BaseTable<*>> Database.bulkInsertOrUpdate(table: T, block: BulkInsertOrUpdateStatementBuilder<T>.(T) -> Unit): Int(source)

Bulk insert records to the table, determining if there is a key conflict while inserting each of them, and automatically performs updates if any conflict exists.

Usage:

database.bulkInsertOrUpdate(Employees) {
    item {
        set(it.id, 1)
        set(it.name, "vince")
        set(it.job, "engineer")
        set(it.salary, 1000)
        set(it.hireDate, LocalDate.now())
        set(it.departmentId, 1)
    }
    item {
        set(it.id, 5)
        set(it.name, "vince")
        set(it.job, "engineer")
        set(it.salary, 1000)
        set(it.hireDate, LocalDate.now())
        set(it.departmentId, 1)
    }
    onConflict {
        set(it.salary, it.salary + 900)
    }
}

Generated SQL:

insert into t_employee (id, name, job, salary, hire_date, department_id)
values (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?)
on conflict (id) do update set salary = t_employee.salary + ?

By default, the column used in the on conflict statement is the primary key you already defined in the schema definition. If you want, you can specify one or more columns for the on conflict statement as belows:

database.bulkInsertOrUpdate(Employees) {
    item {
        set(it.id, 1)
        set(it.name, "vince")
        set(it.job, "engineer")
        set(it.salary, 1000)
        set(it.hireDate, LocalDate.now())
        set(it.departmentId, 1)
    }
    item {
        set(it.id, 5)
        set(it.name, "vince")
        set(it.job, "engineer")
        set(it.salary, 1000)
        set(it.hireDate, LocalDate.now())
        set(it.departmentId, 1)
    }
    onConflict(it.name, it.job) {
        set(it.salary, it.salary + 900)
    }
}

Generated SQL:

insert into t_employee (id, name, job, salary, hire_date, department_id)
values (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?)
on conflict (name, job) do update set salary = t_employee.salary + ?

Return

the effected row count.

Parameters

table

the table to be inserted.

block

the DSL block used to construct the expression.