Diesel: Support for insert into tbl1 from select * from tbl2 queries

[lancecarlson]

Is this something diesel would/should support? (Correct me if Diesel already supports this)

[lancecarlson]

Thinking this through, I would imagine the syntax might look like this:

diesel::insert(diesel::select_table).into(diesel::insert_into_table);

I'm not sure that the dsl adds anything over the SQL unless it can generate the columns from the struct because (at least in postgres) you have to cast the select output into a table with an explicit column manifest/schema.

[killercup]

If the tables have the exact same columns, you could try

diesel::insert(table1::table.select(table1::all_columns)).into(table2::table)

[sgrif]

Update: https://github.com/diesel-rs/diesel/commit/73778af0 was very much in service of this eventual feature. Any version of this that lands will require explicitly listing the columns. e.g. We will not support INSERT INTO table SELECT ... but we will support INSERT INTO table (col1, col2) SELECT ....

I would love to see ideas on concrete API proposals for this. Right now the best I've got is:

insert_into(table1)
    .from_select(
        (col1, col2, col3),
        table2.select(...).etc,
    )

I think we can do better though. In particular, I don't like the amount of rightward drift there. It reminds me very much of our old on_conflict function from 0.16, which we deprecated in 0.99 for the same ergonomic reasons

[sgrif]

I think I'm going to go with this API:

insert_into(table1)
    .from_select(table2.select(...))
    .into_columns((col1, col2, col3))

The order is backwards from what you'd write in SQL, but it lets us default to table1::all_columns, and any method that allowed you to specify the columns doesn't make sense unless you know that you're inserting from a select statement.

[sgrif]

So I prototyped this out, and found that the select statement generally was so long we had the same rightward drift problem. You can work around it by sticking stuff in locals, but then the variable is awkward to name and really "you can work around this crappy API" doesn't make it acceptable. @weiznich had a really good idea in the gitter room though. select_statement.insert_into(table).columns(col_list), which is even more backwards from the SQL, but definitely lets you insert line breaks where it's natural. TBH this is also pretty close to how I read these queries anyway, since the select statement is by far the most important part.

I'm going to play around with it some more, but I think I want to go with that API. The main issue will be making it discoverable. It'll have to end up being a trait that's implemented on BoxedSelectStatement, SelectStatement, and Table rather than an inherent method on IncompleteInsertStatement. I suppose linking to it from the docs, and adding a section to the insert guide will probably be sufficient.

[sgrif]

I ended up generalizing the API. The only API that is being explicitly added for this feature is the into_columns method, which only works for insert from select. Otherwise, passing a select statement to values will now "just work", and any query that can be written as insert_into(table).values(values) can instead be written as values.insert_into(table)