INSERT statement
The INSERT statement can be used to insert or update data into the database, using the same statement syntax as the traditional SQL Insert statement.
Note: This statement can not be used to create graph relationships. For that, use the RELATE statement.
Statement syntax
SurrealQL SyntaxINSERT [ IGNORE ] [INTO] [RELATION] @what
[ @value
| (@fields) VALUES (@values)
[ ON DUPLICATE KEY UPDATE @field = @value ... ]
]
;
Example usage
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']
};
As with the traditional SQL insert statement syntax, records can be inserted by specifying the fields and values by using the VALUES keyword.
-- Insert a single record
INSERT company (name, founded) VALUES ('SurrealDB', '2021-09-10');
-- Insert multiple records
INSERT 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: "person:jaime", name: "Jaime", surname: "Morgan Hitchcock"},
{id: "person:tobie", name: "Tobie", surname: "Morgan Hitchcock"},
]
Bulk insert
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 person [
{id: "person:jaime", name: "Jaime", surname: "Morgan Hitchcock"},
{id: "person:tobie", name: "Tobie", surname: "Morgan Hitchcock"},
... 1000 more records
]
Insert relation tables
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: person:1 },
{ id: person:2 },
{ id: person: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;