logo
pub struct InsertStatement { /* private fields */ }
Expand description

Insert any new rows into an existing table

Examples

use sea_query::{tests_cfg::*, *};

let query = Query::insert()
    .into_table(Glyph::Table)
    .columns([Glyph::Aspect, Glyph::Image])
    .values_panic(vec![5.15.into(), "12A".into()])
    .values_panic(vec![4.21.into(), "123".into()])
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"INSERT INTO `glyph` (`aspect`, `image`) VALUES (5.15, '12A'), (4.21, '123')"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"INSERT INTO "glyph" ("aspect", "image") VALUES (5.15, '12A'), (4.21, '123')"#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"INSERT INTO "glyph" ("aspect", "image") VALUES (5.15, '12A'), (4.21, '123')"#
);

Implementations

Construct a new InsertStatement

Use REPLACE instead of INSERT

Examples
use sea_query::{tests_cfg::*, *};

let query = Query::insert()
    .replace()
    .into_table(Glyph::Table)
    .columns([Glyph::Aspect, Glyph::Image])
    .values_panic(vec![5.15.into(), "12A".into()])
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"REPLACE INTO `glyph` (`aspect`, `image`) VALUES (5.15, '12A')"#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"REPLACE INTO "glyph" ("aspect", "image") VALUES (5.15, '12A')"#
);

Specify which table to insert into.

Examples

See InsertStatement::values

Specify what columns to insert.

Examples

See InsertStatement::values

Specify a row of values to be inserted.

Examples
use sea_query::{tests_cfg::*, *};

let query = Query::insert()
    .into_table(Glyph::Table)
    .columns([Glyph::Aspect, Glyph::Image])
    .values(vec![2.1345.into(), "24B".into()])
    .unwrap()
    .values_panic(vec![5.15.into(), "12A".into()])
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"INSERT INTO `glyph` (`aspect`, `image`) VALUES (2.1345, '24B'), (5.15, '12A')"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"INSERT INTO "glyph" ("aspect", "image") VALUES (2.1345, '24B'), (5.15, '12A')"#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"INSERT INTO "glyph" ("aspect", "image") VALUES (2.1345, '24B'), (5.15, '12A')"#
);

Specify a select query whose values to be inserted.

Examples
use sea_query::{tests_cfg::*, *};

let query = Query::insert()
    .into_table(Glyph::Table)
    .columns([Glyph::Aspect, Glyph::Image])
    .select_from(Query::select()
        .column(Glyph::Aspect)
        .column(Glyph::Image)
        .from(Glyph::Table)
        .and_where(Expr::col(Glyph::Image).like("0%"))
        .to_owned()
    )
    .unwrap()
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"INSERT INTO `glyph` (`aspect`, `image`) SELECT `aspect`, `image` FROM `glyph` WHERE `image` LIKE '0%'"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"INSERT INTO "glyph" ("aspect", "image") SELECT "aspect", "image" FROM "glyph" WHERE "image" LIKE '0%'"#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"INSERT INTO "glyph" ("aspect", "image") SELECT "aspect", "image" FROM "glyph" WHERE "image" LIKE '0%'"#
);

Specify a row of values to be inserted.

Examples
use sea_query::{tests_cfg::*, *};

let query = Query::insert()
    .into_table(Glyph::Table)
    .columns([Glyph::Aspect, Glyph::Image])
    .exprs(vec![
        Expr::val(2).into(),
        Func::cast_as("2020-02-02 00:00:00", Alias::new("DATE")),
    ])
    .unwrap()
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"INSERT INTO `glyph` (`aspect`, `image`) VALUES (2, CAST('2020-02-02 00:00:00' AS DATE))"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"INSERT INTO "glyph" ("aspect", "image") VALUES (2, CAST('2020-02-02 00:00:00' AS DATE))"#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"INSERT INTO "glyph" ("aspect", "image") VALUES (2, CAST('2020-02-02 00:00:00' AS DATE))"#
);

Specify a row of values to be inserted, variation of InsertStatement::values.

Specify a row of values to be inserted, variation of InsertStatement::exprs.

ON CONFLICT expression

Examples

RETURNING expressions.

Examples
use sea_query::{tests_cfg::*, *};

let query = Query::insert()
    .into_table(Glyph::Table)
    .columns([Glyph::Image])
    .values_panic(vec!["12A".into()])
    .returning(Query::returning().columns([Glyph::Id]))
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    "INSERT INTO `glyph` (`image`) VALUES ('12A')"
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"INSERT INTO "glyph" ("image") VALUES ('12A') RETURNING "id""#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"INSERT INTO "glyph" ("image") VALUES ('12A') RETURNING "id""#
);

RETURNING expressions for a column.

Examples
use sea_query::{tests_cfg::*, *};

let query = Query::insert()
    .into_table(Glyph::Table)
    .columns([Glyph::Image])
    .values_panic(vec!["12A".into()])
    .returning_col(Glyph::Id)
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    "INSERT INTO `glyph` (`image`) VALUES ('12A')"
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"INSERT INTO "glyph" ("image") VALUES ('12A') RETURNING "id""#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"INSERT INTO "glyph" ("image") VALUES ('12A') RETURNING "id""#
);

RETURNING expressions all columns.

Examples
use sea_query::{tests_cfg::*, *};

let query = Query::insert()
    .into_table(Glyph::Table)
    .columns([Glyph::Image])
    .values_panic(vec!["12A".into()])
    .returning_all()
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    "INSERT INTO `glyph` (`image`) VALUES ('12A')"
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"INSERT INTO "glyph" ("image") VALUES ('12A') RETURNING *"#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"INSERT INTO "glyph" ("image") VALUES ('12A') RETURNING *"#
);

Create a WithQuery by specifying a WithClause to execute this query with.

Examples
use sea_query::{*, IntoCondition, IntoIden, tests_cfg::*};

let select = SelectStatement::new()
        .columns([Glyph::Id, Glyph::Image, Glyph::Aspect])
        .from(Glyph::Table)
        .to_owned();
    let cte = CommonTableExpression::new()
        .query(select)
        .column(Glyph::Id)
        .column(Glyph::Image)
        .column(Glyph::Aspect)
        .table_name(Alias::new("cte"))
        .to_owned();
    let with_clause = WithClause::new().cte(cte).to_owned();
    let select = SelectStatement::new()
        .columns([Glyph::Id, Glyph::Image, Glyph::Aspect])
        .from(Alias::new("cte"))
        .to_owned();
    let mut insert = Query::insert();
    insert
        .into_table(Glyph::Table)
        .columns([Glyph::Id, Glyph::Image, Glyph::Aspect])
        .select_from(select)
        .unwrap();
    let query = insert.with(with_clause);

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"WITH `cte` (`id`, `image`, `aspect`) AS (SELECT `id`, `image`, `aspect` FROM `glyph`) INSERT INTO `glyph` (`id`, `image`, `aspect`) SELECT `id`, `image`, `aspect` FROM `cte`"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"WITH "cte" ("id", "image", "aspect") AS (SELECT "id", "image", "aspect" FROM "glyph") INSERT INTO "glyph" ("id", "image", "aspect") SELECT "id", "image", "aspect" FROM "cte""#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"WITH "cte" ("id", "image", "aspect") AS (SELECT "id", "image", "aspect" FROM "glyph") INSERT INTO "glyph" ("id", "image", "aspect") SELECT "id", "image", "aspect" FROM "cte""#
);

Insert with default values if columns and values are not supplied.

Examples
use sea_query::{tests_cfg::*, *};

// Insert default
let query = Query::insert()
    .into_table(Glyph::Table)
    .or_default_values()
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"INSERT INTO `glyph` VALUES ()"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"INSERT INTO "glyph" VALUES (DEFAULT)"#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"INSERT INTO "glyph" DEFAULT VALUES"#
);

// Ordinary insert as columns and values are supplied
let query = Query::insert()
    .into_table(Glyph::Table)
    .or_default_values()
    .columns([Glyph::Image])
    .values_panic(vec!["ABC".into()])
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"INSERT INTO `glyph` (`image`) VALUES ('ABC')"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"INSERT INTO "glyph" ("image") VALUES ('ABC')"#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"INSERT INTO "glyph" ("image") VALUES ('ABC')"#
);

Insert multiple rows with default values if columns and values are not supplied.

Examples
use sea_query::{tests_cfg::*, *};

// Insert default
let query = Query::insert()
    .into_table(Glyph::Table)
    .or_default_values_many(3)
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"INSERT INTO `glyph` VALUES (), (), ()"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"INSERT INTO "glyph" VALUES (DEFAULT), (DEFAULT), (DEFAULT)"#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"INSERT INTO "glyph" DEFAULT VALUES"#
);

// Ordinary insert as columns and values are supplied
let query = Query::insert()
    .into_table(Glyph::Table)
    .or_default_values_many(3)
    .columns([Glyph::Image])
    .values_panic(vec!["ABC".into()])
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"INSERT INTO `glyph` (`image`) VALUES ('ABC')"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"INSERT INTO "glyph" ("image") VALUES ('ABC')"#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"INSERT INTO "glyph" ("image") VALUES ('ABC')"#
);

Trait Implementations

Returns a copy of the value. Read more

Performs copy-assignment from source. Read more

Formats the value using the given formatter. Read more

Returns the “default value” for a type. Read more

Build corresponding SQL statement into the SqlWriter for certain database backend and collect query parameters

Build corresponding SQL statement for certain database backend and collect query parameters into a vector

Build corresponding SQL statement for certain database backend and collect query parameters

Build corresponding SQL statement for certain database backend and collect query parameters

Examples
use sea_query::{tests_cfg::*, *};

let query = Query::insert()
    .into_table(Glyph::Table)
    .columns([Glyph::Aspect, Glyph::Image])
    .values_panic(vec![3.1415.into(), "041080".into()])
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"INSERT INTO `glyph` (`aspect`, `image`) VALUES (3.1415, '041080')"#
);

let mut params = Vec::new();
let mut collector = |v| params.push(v);

assert_eq!(
    query.build_collect(MysqlQueryBuilder, &mut collector),
    r#"INSERT INTO `glyph` (`aspect`, `image`) VALUES (?, ?)"#
);
assert_eq!(
    params,
    vec![
        Value::Double(Some(3.1415)),
        Value::String(Some(Box::new(String::from("041080")))),
    ]
);

Build corresponding SQL statement for certain database backend and return SQL string Read more

Build corresponding SQL statement for certain database backend and collect query parameters into a vector Read more

Auto Trait Implementations

Blanket Implementations

Gets the TypeId of self. Read more

Immutably borrows from an owned value. Read more

Mutably borrows from an owned value. Read more

Returns the argument unchanged.

Instruments this type with the provided Span, returning an Instrumented wrapper. Read more

Instruments this type with the current Span, returning an Instrumented wrapper. Read more

Calls U::from(self).

That is, this conversion is whatever the implementation of From<T> for U chooses to do.

Should always be Self

The resulting type after obtaining ownership.

Creates owned data from borrowed data, usually by cloning. Read more

Uses borrowed data to replace owned data, usually by cloning. Read more

The type returned in the event of a conversion error.

Performs the conversion.

The type returned in the event of a conversion error.

Performs the conversion.

Attaches the provided Subscriber to this type, returning a WithDispatch wrapper. Read more

Attaches the current default Subscriber to this type, returning a WithDispatch wrapper. Read more