Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
INSERT .. VALUES
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
INSERT .. VALUES with a single row
Records can either be supplied using a VALUES() constructor, or a SELECT statement. jOOQ supports both types of INSERT statements. An example of an INSERT statement using a VALUES() constructor is given here:
INSERT INTO AUTHOR
(ID, FIRST_NAME, LAST_NAME)
VALUES (100, 'Hermann', 'Hesse');
create.insertInto(AUTHOR,
AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.values(100, "Hermann", "Hesse")
.execute();
Note that for explicit degrees up to 22, the VALUES() constructor provides additional typesafety. The following example illustrates this:
InsertValuesStep3<AuthorRecord, Integer, String, String> step =
create.insertInto(AUTHOR, AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME);
step.values("A", "B", "C");
// ^^^ Doesn't compile, the expected type is Integer
INSERT .. VALUES with multiple rows
The SQL standard specifies that multiple rows can be supplied to the VALUES() constructor in an INSERT statement. Here's an example of a multi-record INSERT
INSERT INTO AUTHOR
(ID, FIRST_NAME, LAST_NAME)
VALUES (100, 'Hermann', 'Hesse'),
(101, 'Alfred', 'Döblin');
create.insertInto(AUTHOR,
AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.values(100, "Hermann", "Hesse")
.values(101, "Alfred", "Döblin")
.execute()
jOOQ tries to stay close to actual SQL. In detail, however, Java's expressiveness is limited. That's why the values() clause is repeated for every record in multi-record inserts.
Some RDBMS do not support inserting several records in a single statement. In those cases, jOOQ emulates multi-record INSERTs using the following SQL:
INSERT INTO AUTHOR
(ID, FIRST_NAME, LAST_NAME)
SELECT 100, 'Hermann', 'Hesse' FROM DUAL UNION ALL
SELECT 101, 'Alfred', 'Döblin' FROM DUAL;
create.insertInto(AUTHOR,
AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.values(100, "Hermann", "Hesse")
.values(101, "Alfred", "Döblin")
.execute();
If your inserted rows or records are dynamic, you can use valuesOfRows() or valuesOfRecords() instead:
INSERT INTO AUTHOR
(ID, FIRST_NAME, LAST_NAME)
VALUES (100, 'Hermann', 'Hesse'),
(101, 'Alfred', 'Döblin');
List<Record3<Integer, String, String>> records = ...
create.insertInto(AUTHOR,
AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.valuesOfRecords(records)
.execute();
Dialect support
This example using jOOQ:
insertInto(AUTHOR).columns(AUTHOR.ID, AUTHOR.LAST_NAME).values(100, "Hesse")
Translates to the following dialect specific expressions:
All dialects
INSERT INTO AUTHOR (ID, LAST_NAME) VALUES ( 100, 'Hesse' )
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!