Insert into + select from using a string based sub-query and a TableImpl<Record>

[Max Kremer]

Hi,

   What I'm trying to accomplish is to build a simple INSERT INTO (....) SELECT ... FROM type query.

The table I'm inserting into has a TableImpl<Record> implementation. The select query does not, its a SQL string I've wrapped in DSL.table().

My jooq TableImpl<Record> look like this:

public class TenantTable extends TableImpl<Record> {

public static final TenantTable TENANT_TABLE = new TenantTable();

public final TableField<Record, String> API_TOKEN = createField("api_token", SQLDataType.VARCHAR.length(36), this);

public final TableField<Record, EventType> EVENT_TYPE = createField("event_type", SQLDataType.VARCHAR.length(16), this, null, EventTypeConverter.INSTANCE);

public final TableField<Record, Integer> YEAR = createField("year", SQLDataType.INTEGER, this);

public final TableField<Record, Integer> MONTH = createField("month", SQLDataType.INTEGER, this);

public TenantTable() {

super("tenant_table");

}

}

And my table-like subquery look like this

Table<Record> foo = table("SELECT A, B, C, D FROM Foo");  //simplification but u get the idea

I'm trying to do something like this....

dslContext.insertInto(TENANT_TABLE, TENANT_TABLE.EVENT_TYPE, TENANT_TABLE.API_TOKEN, TENANT_TABLE.MONTH, TENANT_TABLE.YEAR).select(selectFrom(foo));

Obviously its nowhere near compiling...

ultimately I'm trying to generate the following SQL:

INSERT INTO tenant(api_token, event_type, year, month) select A,B,C,D FROM Foo;

How do I accomplish the above?

Thanks!

[Lukas Eder]

You're close.

Replace:

    selectFrom(foo)

With

    select(FOO.A, FOO.B, FOO.C, FOO.D).from(FOO)

If the individual column types match, you're done.

Hope this helps,
Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Max Kremer]

Hi Lukas,

  That's not going to work.... There is no FOO.A, FOO.B etc... it looks like this

Table<Record> foo = table("SELECT A, B, C, D FROM Foo");

The table I'm inserting into is class that extends TableImpl<Record> so it's well defined.
But the table I'm selecting from is just string. I used DSL.table() to create it.

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

Oh, I see. Then you have these two options. Both will work around the type safety:

dslContext.insertInto(

    TENANT_TABLE, new Field[] { 

        TENANT_TABLE.EVENT_TYPE, 

        TENANT_TABLE.API_TOKEN, 

        TENANT_TABLE.MONTH, 

        TENANT_TABLE.YEAR

    }).select(selectFrom(foo));

dslContext.insertInto(

    TENANT_TABLE, 

    TENANT_TABLE.EVENT_TYPE, 

    TENANT_TABLE.API_TOKEN, 

    TENANT_TABLE.MONTH, 

    TENANT_TABLE.YEAR

).select(selectFrom((Table) foo));

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.

[Max Kremer]

Thanks Lukas! That worked. I went with the first solution to avoid type safety warnings.