INSERT
statementThe INSERT
statement can be used to insert or update data into the database, using the same statement syntax as the traditional SQL Insert statement.
SurrealQL SyntaxINSERT [ IGNORE | RELATION ] INTO @what [ @value | (@fields) VALUES (@values) [ ON DUPLICATE KEY UPDATE @field = @value ... ] ] [ RETURN NONE | RETURN BEFORE | RETURN AFTER | RETURN DIFF | RETURN @statement_param, ... | RETURN VALUE @statement_param ] ;
The following query shows example usage of this statement.
INSERT INTO company { name: 'SurrealDB', founded: "2021-09-10", founders: [person:tobie, person:jaime], tags: ['big data', 'database'] };
Records can also be inserted by using the VALUES
keyword. This keyword is preceded by the name of the fields in question, and followed by comma-separated values matching the number of fields specified.
-- Insert a single record INSERT INTO company (name, founded) VALUES ('SurrealDB', '2021-09-10'); -- Insert multiple records INSERT INTO company (name, founded) VALUES ('Acme Inc.', '1967-05-03'), ('Apple Inc.', '1976-04-01');
When using the VALUES
clause, it is possible to update records which already exist by specifying an ON DUPLICATE KEY UPDATE
clause. This clause also allows incrementing and decrementing numeric values, and adding or removing values from arrays. To increment a numeric value, or to add an item to an array, use the +=
operator. To decrement a numeric value, or to remove an value from an array, use the -=
operator.
INSERT INTO product (name, url) VALUES ('Salesforce', 'salesforce.com') ON DUPLICATE KEY UPDATE tags += 'crm';
Field names inside ON DUPLICATE KEY UPDATE
refer to the fields of the existing record. To access the fields of the new record that was attempted to be inserted, prefix the field name with $input
:
INSERT INTO city (id, population, at_year) VALUES ("Calgary", 1665000, 2024) ON DUPLICATE KEY UPDATE population = $input.population, at_year = $input.at_year;
Using the insert statement, it is possible to copy records easily between tables. The records being copied will have the same id in the new table, but the record id will signify the new table name.
INSERT INTO recordings_san_francisco (SELECT * FROM temperature WHERE city = 'San Francisco');
Furthermore, it is possible to perform a bulk insert in a single query. The @what
part of the syntax can be either a single object or an array of objects.
INSERT INTO person [ { id: "jaime", name: "Jaime", surname: "Morgan Hitchcock" }, { id: "tobie", name: "Tobie", surname: "Morgan Hitchcock" }, ]
Available since: v2.0.0
By default, the INSERT
statement returns the record once it has been inserted. To change what is returned, we can use the RETURN
clause, specifying either NONE
, BEFORE
, AFTER
, DIFF
, or a comma-separated list of specific fields to return.
RETURN NONE
can be useful to avoid excess output:
-- Insert a record and return nothing INSERT INTO company { name: 'SurrealDB', founded: "2021-09-10", founders: [person:tobie, person:jaime], tags: ['big data', 'database'] } RETURN NONE;
RETURN DIFF
returns the changeset diff:
-- Insert a record and return the diff INSERT INTO company { name: 'SurrealDB', founded: "2021-09-10", founders: [person:tobie, person:jaime], tags: ['big data', 'database'] } RETURN DIFF;
Response-------- Query 1 (500µs) -------- [ [ { op: 'replace', path: '/', value: { founded: '2021-09-10', founders: [ person:tobie, person:jaime ], id: company:hu5o1wqbo29t10engbeo, name: 'SurrealDB', tags: [ 'big data', 'database' ] } } ] ]
RETURN BEFORE
inside a INSERT
statement is essentially a synonym for RETURN NONE
, while RETURN AFTER
is the default behaviour for INSERT
.
-- Before insert will always return NONE as it is the same as the record being inserted INSERT INTO company { name: 'SurrealDB', founded: "2021-09-10", founders: [person:tobie, person:jaime], tags: ['big data', 'database'] } RETURN BEFORE;
-- Return the record after creation INSERT INTO company { name: 'SurrealDB', founded: "2021-09-10", founders: [person:tobie, person:jaime], tags: ['big data', 'database'] } RETURN AFTER;
You can also return specific fields from a created record, as well as ad-hoc fields to modify the output as needed.
INSERT INTO person { age: 46, username : "john-smith", interests : ['skiing', 'music'] } RETURN age, interests, age + 1 AS age_next_year;
Response[ { age: 46, age_next_year: 47, interests: [ 'skiing', 'music' ] } ]
Available since: v2.0.0
The INSERT
statement supports bulk insert, which allows multiple records to be inserted in a single query. The @what
part of the syntax can be either a single object or an array of objects.
INSERT INTO person [ { id: "jaime", name: "Jaime", surname: "Morgan Hitchcock" }, { id: "tobie", name: "Tobie", surname: "Morgan Hitchcock" }, -- ... 1000 more records ]
The INSERT
statement can also be used to add records into relation tables. The @what
part of the syntax can be either a single object or an array of objects.
Learn more about creating relationships between tables in the RELATE statement. For example:
-- Insert records into the person table INSERT INTO person [ { id: 1 }, { id: 2 }, { id: 3 }, ]; -- Insert a single relation INSERT RELATION INTO likes { in: person:1, id: 'object', out: person:2, }; -- Insert multiple relations INSERT RELATION INTO likes [ { in: person:1, id: 'array', out: person:2, }, { in: person:2, id: 'array_two', out: person:3, } ]; -- Insert a relation and return the value of the likes field INSERT RELATION INTO likes (in, id, out) VALUES (person:1, 'values', person:2); -- Select the value of the likes field SELECT VALUE ->likes FROM person;