Slick:Insert into a Table from Raw SQL Select

Question

Insert into a Table from Raw SQL Select

val rawSql: DBIO[Vector[(String, String)]] = sql"SELECT id, name FROM SomeTable".as[(String, String)]
val myTable :TableQuery[MyClass] // with columns id (String), name(String) and some other columns

Is there a way to use forceInsert functions to insert data from select into the tables? If not, Is there a way to generate a sql string by using forceInsertStatements? Something like:

db.run {
   myTable.map{ t => (t.id, t.name)}.forceInsert????(rawSql)
}

P.S. I don't want to make two I/O calls because my RAW SQL might be returning thousands of records. Thanks for the help.

score 1 · Accepted Answer · answered Aug 24 '20 at 14:03

If you can represent your rawSql query as a Slick query instead...

val query = someTable.map(row => (row.id, row.name))

...for example, then forceInsertQuery will do what you need. An example might be:

  val action =
     myTable.map(row => (row.someId, row.someName))
      .forceInsertQuery(
        someTable.map(query)
      )

However, I presume you're using raw SQL for a good reason. In that case, I don't believe you can use forceInsert (without a round-trip to the database) because the raw SQL is already an action (not a query).

But, as you're using raw SQL, why not do the whole thing in raw SQL? Something like:

 val rawEverything = 
  sqlu" insert into mytable (someId, someName) select id, name from sometable "

...or similar.

Slick:Insert into a Table from Raw SQL Select

1 Answers1