I’m attempting to replicate an INSERT … SELECT in Ecto. This appears to be supported by both postgres and mysql (I’m using postgres). I’ve got this working in SQL, but trying to recreate in Ecto.Query. I created an ecto query that returns the data I want, and tried passing it to insert or insert_al…

Since this reply still getting lots of clicks and this thread is the top result on google I’ll post an update here. José pointed out on the last PR I linked that there is a new feature that might address this feature. Ecto.Repo — Ecto v3.14.0

Can you share the working sql query? I suspect you might be looking for Ecto.Query — Ecto v3.14.0

A very simplified example (but I’m only adding more columns): INSERT INTO fake_table (name, inserted_at, updated_at) SELECT name, now(), now() FROM other_fake_table;

I’m not sure if this is the right syntax but maybe you can do something along the lines of Repo.insert_all( SomeSchema, [ [id: from(o in OtherSchema, select: o.id)] ] )

insert_all does support using a query, similar to what you’ve suggested, but only to return a single value. It doesn’t appear to build/use the INSERT INTO … SELECT statement. The keys of the entries are the field names as atoms and the value should be the respective value for the field type or, op…

Hey! I forgot about this reply :blush: [image] tpitale: insert_all does support using a query, similar to what you’ve suggested, but only to return a single value Well, thats a bummer. How did you ended up solving this?

In the end: SQL. I may try to submit a feature to ecto.

[image] tpitale: In the end: SQL. Yeah, figured that might be the case. [image] tpitale: I may try to submit a feature to ecto. Found the PR that implemented the suporte for single return subquery on insert_all. Maybe I will have a look this weekend to see if its easy to implement th…

INSERT … SELECT in Ecto using Ecto.Query

Questions & Help Questions

cpgo August 26, 2020, 12:20pm 6

Hey! I forgot about this reply

Well, thats a bummer. How did you ended up solving this?