JOOQ insert into select syntax with specified columns

Question

Can JOOQ do 'insert into select' syntax for specified columns? I run several different tries..

The Tables:

table1 (Expected insert)

id   column1   column2    column3 ...  timestamp
1    John      Leo        null         2012/1/28 23:32:23    (Expected insert)

table2

id   col1   col2   col3  
101  John   xxx    xxx     (from table2.col1)
102  xxx    xxx    xxx

table3

id   col1   col2   col3  
101  xxx    Leo    xxx     (from table3.col2)
102  xxx    xxx    xxx

INSERT INTO table1 ( column1, column2 )
SELECT  table2.col1, table3.col2
FROM table2 join table3 t3 on table2.id = table3.id 
where table2.id = 101;

JOOQ code:

create.insertInto(table1, column1, column2 )
      .values( create.select( table2.col1, table3.col2 )
                     .from(table2)
                     .join(table3)
                     .on( table12.id.equal(table3.id) )
                     .where( table2.id.equal(101) ))
     .execute(); //.getSQL();

JOOQ show error message:

The number of values must match the number of fields

Anyone know what problem I make and how I can fix my JOOQ code. thanks, Pay.

Reference: Example: INSERT SELECT syntax support

Answer 1

You're using the INSERT .. VALUES syntax, instead of the INSERT .. SELECT syntax. Your subquery provides values column1 but you don't provide any value for column2. What you want to do is described further down in the manual at "Example: INSERT SELECT syntax support". In your case, this would read (jOOQ 2.x syntax, no longer available in jOOQ 3.x):

create.insertInto(table1, 
create.select( table2.col1, table3.col2 )
      .from(table2)
      .join(table3)
      .on( table12.id.equal(table3.id) )
      .where( table2.id.equal(101) ))
      .execute(); //.getSQL();

Or with a custom projection (jOOQ 3.x syntax):

create.insertInto(table1, column1, column2)
      .select(create.select(...))
      .execute();