Set the columns to return (RETURNING), read with #get; for dialects that supports_returning?

• @param columns [Symbol*] The columns to return
• @return [Insert] The insert object
• @raise [Exception] If the column does not exist

Example Inserting a record

AcmeDB.insert.into(:users).values(name: "John", email: "john@example.com").back(:id)
  .to_sql(Expression::Generator.new(CQL::Adapter::Postgres))
# => {"INSERT INTO users (name, email) VALUES ($1, $2) RETURNING users.id", ["John", "john@example.com"]}

Build the insert statement object @return [Expression::Insert] The insert statement

Example Building the insert statement

AcmeDB.insert.into(:users).values(name: "John", email: "john@example.com").build

Executes the insert statement and returns the result The query execution is automatically tracked by CQL::Performance when enabled.

• @return [DB::ExecResult] The rows affected and the last inserted ID

Example Inserting a record

result = AcmeDB.insert
  .into(:users)
  .values(name: "John", email: "john@example.com")
  .commit
result.rows_affected

Updates the existing row: refresh columns take the incoming value (all non-target columns when none are given), set columns take explicit values.

Inserts and reads the row returned by the RETURNING clause (#back) as kind, nil when nothing was inserted (on_conflict(...).do_nothing). PostgreSQL and CockroachDB only.

• @raise [CQL::DialectError] Before inserting, when the dialect does not supports_returning?
• @raise [ArgumentError] Before inserting, when no #back columns are set

AcmeDB.insert.into(:users).values(name: "John", email: "john@example.com").back(:id, :name).get({Int64, String})

Set the table to insert into

• @param table [Symbol] The table to insert into
• @return [Insert] The insert object
• @raise [ArgumentError] If the table does not exist

Example Inserting a record

AcmeDB.insert.into(:users)

Inserts and gets the last inserted ID from the database Works with SQLite, PostgreSQL and MySQL. The query execution is automatically tracked by CQL::Performance when enabled.

• @return [Int64] The last inserted ID

Example Getting the last inserted ID

AcmeDB.insert.into(:users).values(name: "John", email: "john@example.com").last_insert_id

Starts an upsert: what to do when a row with the same columns already exists. Follow with #do_nothing or #do_update.

AcmeDB.insert.into(:users).values(email: "john@example.com", name: "John").on_conflict(:email).do_update(:name)

Set the query whose rows are inserted (INSERT INTO ... SELECT ...); its selected columns name the inserted columns

• @param query [Query] The query to use
• @return [Insert] The insert object

Example Inserting the rows a query selects

source = AcmeDB.query.from(:users).select(:name, :email).where { users.id == 1 }
AcmeDB.insert.into(:users).query(source).to_sql
# => {"INSERT INTO users (name, email) SELECT users.name, users.email FROM users WHERE users.id = ?", [1]}

The insert with RETURNING the table's declared primary key (id when none is declared), as {sql, params}. PostgreSQL and CockroachDB fetch the new key this way.

Convert the insert object to a SQL query

• @param gen [Generator] The generator to use
• @return [{String, Array(DB::Any)}] The query and parameters
• @raise [Exception] If the table does not exist

Example Generating a SQL query

AcmeDB.insert.into(:users).values(name: "John", email: "john@example.com").to_sql
# => {"INSERT INTO users (name, email) VALUES (?, ?)", ["John", "john@example.com"]}

Inserts many rows, updating update columns (default: every other column; an empty list means do nothing) of rows that already exist for conflict. Model.upsert_all passes its own default, which also keeps id and created_at.

Set the rows to insert, one hash per row; every row must name the same columns

• @param values [Array(Hash(Symbol, DB::Any))] The rows to insert
• @return [Insert] The insert object

Example Inserting several records

rows = [
  {:name => "John", :email => "john@example.com"} of Symbol => DB::Any,
  {:name => "Jane", :email => "jane@example.com"} of Symbol => DB::Any,
]
AcmeDB.insert.into(:users).values(rows).commit

Set the values to insert

• @param hash [Hash(Symbol, DB::Any)] The values to insert
• @return [Insert] The insert object

Example Inserting a record

row = {:name => "John", :email => "john@example.com"} of Symbol => DB::Any
AcmeDB.insert.into(:users).values(row).commit

Set the values to insert

• @param fields [NamedTuple] The values to insert, keyed by column
• @return [Insert] The insert object

Example Inserting a record

AcmeDB.insert.into(:users).values(name: "John", email: "john@example.com").commit