INSERT | Statements

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. To compare it with the other statements that write records, see Writing records that may already exist.

Statement syntax

SurrealQL Syntax

INSERT [ RELATION ] [ IGNORE ] 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 ]
;

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']
};

The object comes directly after the table name, with no CONTENT keyword. CREATE, UPDATE and RELATE take one clause (CONTENT, SET, MERGE and so on) that applies the same change to every record they write. INSERT takes the data for each record instead, as one object or an array of objects that can each have different fields, so there is no clause to name. Writing CONTENT anyway fails with an error that does not mention it, because the parser reads CONTENT as the value to insert:

INSERT INTO company CONTENT { name: 'SurrealDB' };

Error output

Parse error: Unexpected token `{`, expected Eof

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');

It is possible to update records which already exist or violate a unique index 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 a 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;

An example of ON DUPLICATE KEY UPDATE when a unique key is encountered shows the same behaviour as that with a duplicate record:

DEFINE FIELD data_for ON user_data TYPE record<user>;
DEFINE INDEX one_user ON user_data FIELDS data_for UNIQUE;

INSERT INTO user_data {
    data_for: user:one,
    some: "data"
} ON DUPLICATE KEY UPDATE times_updated += 1, last_edited = time::now();

INSERT INTO user_data {
    data_for: user:one,
    some_more: "data"
} ON DUPLICATE KEY UPDATE times_updated += 1, last_edited = time::now();

Output

-------- Query --------

[
	{
		for: user:one,
		id: user_data:kp78dubsxmp4f04x0de3,
		some: 'data'
	}
]

-------- Query --------

[
	{
		for: user:one,
		id: user_data:kp78dubsxmp4f04x0de3,
		last_edited: d'2025-07-14T05:15:52.146Z',
		some: 'data',
		times_updated: 1
	}
]

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" },
];

Ignoring duplicates

While attempting to insert one or more records via the INSERT statement, if the record ID is already present in the table, the query will encounter an error and fail. If the IGNORE clause is supplied, records with an already existing or duplicate ID will be silently ignored.

INSERT IGNORE INTO person [
   { id: "jaime", name: "Jaime", surname: "Morgan Hitchcock" },
   { id: "tobie", name: "Tobie", surname: "Morgan Hitchcock" },

   { id: "jaime", name: "Jaime", surname: "Morgan Hitchcock" }, -- will not throw an error
];

A unique index violation is skipped in the same way. A record that would break a UNIQUE index is ignored and the rest of the statement still runs, which gives an insert-if-not-exists pattern without a separate check. Without IGNORE, the same statement fails with an error.

DEFINE INDEX unique_email ON user FIELDS email UNIQUE;
INSERT INTO user { id: 'one', email: 'a@example.com' };

-- The first record repeats an existing email and is skipped
INSERT IGNORE INTO user [
	{ id: 'two', email: 'a@example.com' },
	{ id: 'three', email: 'b@example.com' }
];

Output

[{ email: 'b@example.com', id: user:three }]

Only the records that were inserted are returned, so an empty array means that every record was skipped.

Return values

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;

Output

-------- Query 1 --------

[
	[
		{
			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 an 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, the value of a single field using VALUE, 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;

INSERT INTO planet [
	{
		name: 'Venus',
        surface_temp: 462,
        temp_55_km_up: 27
	},
	{
		name: 'Earth',
        surface_temp: 15,
        temp_55_km_up: -55
	}
] RETURN VALUE temp_55_km_up;

Output

-------- Query --------

[
	{
		age: 46,
		age_next_year: 47,
		interests: [
			'skiing',
			'music'
		]
	}
]

-------- Query --------

[
	27,
	-55
]

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 INTO person [
   { id: "jaime", name: "Jaime", surname: "Morgan Hitchcock" },
   { id: "tobie", name: "Tobie", surname: "Morgan Hitchcock" },
   -- ... 1000 more records
];

A bulk insert can also take an ON DUPLICATE KEY UPDATE clause. Each object in the array is inserted in turn, so when the same record ID appears more than once, the first object creates the record and each later one runs the update against the record as it stands at that point. The statement returns the record after each attempt, which shows the update applying once per duplicate. Counting page visits from a batch of events works this way:

INSERT INTO page [
	{ id: page:home, visits: 1 },
	{ id: page:home, visits: 1 },
	{ id: page:home, visits: 1 },
	{ id: page:home, visits: 1 },
	{ id: page:about, visits: 1 },
	{ id: page:about, visits: 1 }
] ON DUPLICATE KEY UPDATE visits += 1;
//- [{ id: page:home, visits: 1 }, { id: page:home, visits: 2 }, { id: page:home, visits: 3 }, { id: page:home, visits: 4 }, { id: page:about, visits: 1 }, { id: page:about, visits: 2 }]

SELECT * FROM page;
//- [{ id: page:about, visits: 2 }, { id: page:home, visits: 4 }]

Choosing the table with a parameter

The table after INTO can be a parameter, which holds either a table name as a string or a table value. A function call cannot go there directly, so INSERT INTO type::table($name) is a parse error. Assign the result to a parameter first:

LET $name = "event_" + "2026";
INSERT INTO $name { kind: "signup" };

LET $table = type::table("person");
INSERT INTO $table { name: "Ann" };

To write records to different tables in one statement, leave out INTO and give each object a full record ID:

INSERT [
	{ id: person:ann, name: "Ann" },
	{ id: company:acme, name: "Acme" }
];

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: 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;

From SurrealDB 3.1.5, when INSERT RELATION specifies an explicit edge id that already exists, SurrealDB returns a record-exists error instead of updating the edge in place. You can use ON DUPLICATE KEY UPDATE to merge into the existing relation, in the same way as a normal INSERT:

CREATE person:one, post:one;

INSERT RELATION INTO likes {
  in: person:one,
  out: post:one,
  id: [person:one, post:one],
  note: 'first',
};

INSERT RELATION INTO likes {
  in: person:one,
  out: post:one,
  id: [person:one, post:one],
  note: 'second',
};

INSERT RELATION INTO likes {
  in: person:one,
  out: post:one,
  id: [person:one, post:one],
  note: 'third',
} ON DUPLICATE KEY UPDATE note = 'updated';