SQL - `INSERT`

The INSERT command creates a new record in the database. Records can be schema-less or follow rules specified in your model.

Syntax:

INSERT INTO [TYPE:]<type>|BUCKET:<bucket>|INDEX:<index>
  [(<field>[,]*) VALUES (<expression>[,]*)[,]*]|
  [SET <field> = <expression>|<sub-command>[,]*]|
  [CONTENT {<JSON>}|[{<JSON>}[,]*]]
  [ON DUPLICATE KEY SKIP]
  [RETURN <expression>]
  [FROM <query>]

SET Abbreviated syntax to set field values.
CONTENT Defines JSON data as an option to set field values of one (object) or multiple (in an array of objects) records.
ON DUPLICATE KEY SKIP When inserting multiple records, skips any record that would violate a unique index instead of aborting the whole command. Each result row tells you whether that record was inserted or skipped. Any other kind of error (for example, a missing mandatory field) still aborts the command as usual.
RETURN Defines an expression to return instead of the number of inserted records. You can use any valid SQL expression. The most common use-cases,
- @rid Returns the Record ID of the new record.
- @this Returns the entire new record.
FROM Defines where you want to insert the result-set.

To insert only documents, vertices, or edges with a certain unique property, use a unique index.

INSERT INTO <timeseries type> is the one INSERT target that is not atomic with the transaction containing it. The samples are committed as they are appended: they are durable and visible to every other connection as soon as the statement returns, and rolling the transaction back does not take them back. Every other INSERT target behaves normally, which is exactly why this one surprises people. See Appends and Transactions for why, and for the same statement about the other ingestion paths.

If multiple documents are inserted as JSON "CONTENT", and one document causes an error, for example due to a schema error, the remaining documents in the JSON array are not inserted. Add ON DUPLICATE KEY SKIP if you only want to skip records that clash with a unique index, instead of aborting the rest of the batch.

Examples

Inserts a new record with the name Jay and surname Miner.

As an example, in the SQL-92 standard, such as with a Relational database, you might use:

ArcadeDB> INSERT INTO Profile (name, surname)
            VALUES ('Jay', 'Miner')

Alternatively, in the ArcadeDB abbreviated syntax, the query would be written as,

ArcadeDB> INSERT INTO Profile SET name = 'Jay', surname = 'Miner'

In JSON content syntax, it would be written as this,

ArcadeDB> INSERT INTO Profile CONTENT {"name": "Jay", "surname": "Miner"}

Insert a new record of the type Profile, but in a different bucket from the default.

In SQL-92 syntax:

ArcadeDB> INSERT INTO Profile BUCKET profile_recent (name, surname) VALUES
            ('Jay', 'Miner')

Alternative, in the ArcadeDB abbreviated syntax:

ArcadeDB> INSERT INTO Profile BUCKET profile_recent SET name = 'Jay',
            surname = 'Miner'

Insert several records at the same time:

ArcadeDB> INSERT INTO Profile (name, surname) VALUES ('Jay', 'Miner'),
            ('Frank', 'Hermier'), ('Emily', 'Sout')

again in JSON content syntax, a JSON array of the to-be-inserted objects is passed:

ArcadeDB> INSERT INTO Profile CONTENT [{"name": "Jay", "surname": "Miner"},
            {"name": "Frank", "surname": "Hermier"},{"name": "Emily", "surname": "Sout"}]

Insert several records, skipping any that clash with a unique index:

ArcadeDB> INSERT INTO Product CONTENT [{"sku": "A1", "name": "Widget"},
            {"sku": "A2", "name": "Gadget"}] ON DUPLICATE KEY SKIP

If Product.sku is a unique index and a record with sku = 'A1' already exists, that record is skipped and the rest are still inserted.

Insert a new record, adding a relationship.

In SQL-92 syntax:

ArcadeDB> INSERT INTO Employee (name, boss) VALUES ('jack', #11:9)

In the ArcadeDB abbreviated syntax:

ArcadeDB> INSERT INTO Employee SET name = 'jack', boss = #11:99

Insert a new record, add a collection of relationships.

In SQL-93 syntax:

ArcadeDB> INSERT INTO Profile (name, friends) VALUES ('Luca', [#10:3, #10:4])

In the ArcadeDB abbreviated syntax:

ArcadeDB> INSERT INTO Profiles SET name = 'Luca', friends = [#10:3, #10:4]

Inserts using SELECT sub-queries

ArcadeDB> INSERT INTO Diver SET name = 'Luca', buddy = (SELECT FROM Diver
            WHERE name = 'Marko')

Inserts using INSERT sub-queries:

ArcadeDB> INSERT INTO Diver SET name = 'Luca', buddy = (INSERT INTO Diver
            SET name = 'Marko')

Inserting into a different bucket:

ArcadeDB> INSERT INTO BUCKET:asiaemployee (name) VALUES ('Matthew')

However, note that the document has no assigned type. To create a document of a certain type, but in a different bucket than the default, instead use:

ArcadeDB> INSERT INTO BUCKET:asiaemployee (@type, content) VALUES
            ('Employee', 'Matthew')

That inserts the document of the type Employee into the bucket asiaemployee.

Insert a new record, adding it as an embedded document:

ArcadeDB> INSERT INTO Profile (name, address) VALUES ('Luca', { "@type": "d",
            "street": "Melrose Avenue"})

Insert a record with a list property:

ArcadeDB> INSERT INTO Profile SET friends = list("Joe","Jack","John")

Insert a record with a map property:

ArcadeDB> INSERT INTO Profile SET address = map("home","Avenue Lane")

Insert from a query.

To copy records from another type, use:

ArcadeDB> INSERT INTO GermanyClient FROM SELECT FROM Client WHERE
            country = 'Germany'

This inserts all the records from the type Client where the country is Germany, in the type GermanyClient.

To copy records from one type into another, while adding a field:

ArcadeDB> INSERT INTO GermanyClient FROM SELECT *, true AS copied FROM Client
            WHERE country = 'Germany'

This inserts all records from the type Client where the country is Germany into the type GermanClient, with the addition field copied to the value true.

Each new record holds exactly the properties of the row the SELECT produced, so the projection decides what is copied: SELECT *, !secret leaves secret out and SELECT *, upper(name) AS name writes the computed name. A row read from a vertex still creates a vertex.

ArcadeDB> INSERT INTO PublicClient FROM SELECT *, !password FROM Client

Before v26.10.2 a row backed by a record (every SELECT * shape) copied the record instead of the row, so an excluded column was copied anyway and an overriding alias was written with the stored value.

The SELECT is evaluated on the data as it was when the statement started, as SQL requires, so it never reads the records the statement inserts. That matters when it reads the type it inserts into, or one of its super types:

ArcadeDB> INSERT INTO Client FROM SELECT id + 1000 AS id, name FROM Client WHERE id >= 4

copies each matching client once. ArcadeDB drains such a SELECT before the first insert, holding its rows in memory, and only when the SELECT can read a bucket the statement writes to (or nests a subquery): a copy between unrelated types streams the rows as before. The rows held count against the heap budget all queries share, so copying a very large type into itself can be refused with a heap-limit error; copy it in ranges (WHERE id BETWEEN …) or through an intermediate type instead.

Before v26.11.1 such a SELECT could read its own inserts: with an OR filter a row was copied twice, and with an index range on the filtered property (WHERE id >= 4 over an index on id) the statement never ended (issue #10192).

Insert a vertex.

Besides the specialized command CREATE VERTEX, vertices and edges can be inserted via the INSERT command:

ArcadeDB> INSERT INTO MyVertexType SET name = 'John Doe'

However, edges have to be created with CREATE EDGE.

SQL - INSERT

SQL - `INSERT`