Is there a way to do a INSERT INTO SELECT query in sea_query?

[zajko]

Hi
I want to init one big table based on a column from another table, possibly involving millions of rows. I'd rather not fetch all that data from DB to RUST and do N sequential inserts. Most DBs nowadays support syntax like:
INSERT INTO "y" ("id") SELECT "id" FROM "x";
(this being an sqlite example)

But I'm not sure how to use the sea-query syntax to achieve that, when I try doing:

let sub_select_x= Query::select()
.expr(Expr::col(X::Id))
.from(X::Table)
.to_owned();
let x_expr = SimpleExpr::SubQuery(Box::new(SubQueryStatement::SelectStatement(
sub_select_x,
)));
let cols = vec![Y::Id];
let exprs = vec![
x_expr,
];
let builder = Query::insert()
.into_table(Y::Table)
.columns(cols.clone())
.exprs(exprs)?
.to_owned();
let sql = builder.to_string(SqliteQueryBuilder)`

I end up with a statement like:
INSERT INTO "X" ("id") VALUES ((SELECT "id" FROM "Y")) (mind the parentesis), which only inserts one value from Y to X
X and Y are defined as:

#[derive(Iden)]
pub(super) enum X {
#[iden = "X"]
Table,
Id,
}

#[derive(Iden)]
pub(super) enum Y {
#[iden = "Y"]
Table,
Id,
}`

Is what I'm trying to do event possible with sea_query?

[billy1624]

Hey @zajko, check this example https://docs.rs/sea-query/latest/sea_query/query/struct.InsertStatement.html#method.select_from

[zajko]

Wow, thanks for the super quick response. How did I not see that 🙇‍♂️

[billy1624]

No problem