Custom SQL query to insert results of a SELECT into a table in Spring Boot JPA Hibernate

Question

I have a spring boot project using JPA hibernate.

I'm trying to make a custom query that inserts the results of a select into a table. How would I do that?

This is my SQL query:

INSERT INTO aggregateTable

  (
    SELECT
      avgSalesA,
      avgSalesB,
      salesMade,
      tableA.employeeid
    FROM

      (
        SELECT
          avg(sales) as avgSalesA,
          count(salesMadeDaily) as SalesMadeTotal,
          employeeid
        FROM companyA
        GROUP BY employeeid
      ) tableA

    INNER JOIN

    (
      SELECT
        avg(sales) as avgSalesB,
        employeeid
      FROM companyB
      GROUP BY employeeid
    ) tableB

    ON tableA.employeeid = tableB.employeeid
  )

Simon Martinelli · Accepted Answer · 2018-09-21 14:26:49Z

1

You could simply use JPA:

entityManager.executeUpdate("INSERT INTO aggregateTable (Select avgSalesA, avgSalesB, 
                             salesMade, tableA.employeeid FROM 
                             (SELECT avg(sales) as avgSalesA, count(salesMadeDaily) as 
                             SalesMadeTotal, employeeid FROM companyA GROUP BY 
                             employeeid) tableA INNER JOIN (SELECT 
                             avg(sales) as avgSalesB, employeeid FROM companyB group 
                             by employeeid) 
                             tableB ON tableA.employeeid = tableB.employeeid)");

Or if you use Spring Data repositories

@Modifying
@Query("<your insert query", nativeQuery=true)
public int insert();

answered Sep 21, 2018 at 14:26

Simon Martinelli

36.6k6 gold badges62 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Custom SQL query to insert results of a SELECT into a table in Spring Boot JPA Hibernate

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related