Back to top
  Back to overview

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.

Statement syntax

INSERT [ IGNORE ] INTO @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 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';

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