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:09)

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.

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`