Insert Data with a Query

Capella Operational

how-to

How to insert documents using SQL++.

Introduction

To insert documents in a keyspace, you can use the INSERT statement, the UPSERT statement, or the MERGE statement.

If you want to try out the examples in this section, follow the instructions given in Create an Account and Deploy Your Free Tier Operational Cluster to create a free account, deploy a cluster, and load a sample dataset. Read the following for further information about the tools available for editing and executing queries:

Please note that the examples in this guide will alter the data in your sample database. To restore your sample data, remove and reinstall the travel sample data. Refer to Import Data with the Capella UI for details.

Inserting a Document

To insert a document by providing the value, use the INSERT statement with the VALUES clause:

Use the INTO keyword to specify the keyspace into which the document is inserted.
Optionally, use the bracketed KEY and VALUE keywords to specify that you’re inserting a document key and body.
Use the VALUES clause to specify the document key and the body of the document.
If required, use the RETURNING clause specifies what the query returns when the document is inserted.

The following query creates a document in the airline keyspace.

Context

Set the query context to the inventory scope in the travel sample dataset. For more information, see Setting the Query Context.

Query

INSERT INTO airline (KEY,VALUE)
  VALUES ( "1025",
            {     "callsign": "MY-AIR",
                  "country": "United States",
                  "iata": "Z1",
                  "icao": "AQZ",
                  "id": "1011",
                  "name": "80-My Air",
                  "type": "airline"
            } )
RETURNING *;

Results

{
  "requestID": "c3bd0276-5d7d-425f-98f9-b333b9ae4302",
  "signature": {
      "*": "*"
  },
  "results": [
  {
      "airline": {
          "callsign": "MY-AIR",
          "country": "United States",
          "iata": "Z1",
          "icao": "AQZ",
          "id": "1011",
          "name": "80-My Air",
          "type": "airline"
      }
  }
  ],
  "status": "success",
  "metrics": {
      "elapsedTime": "5.9133ms",
      "executionTime": "5.6264ms",
      "resultCount": 1,
      "resultSize": 254,
      "serviceLoad": 4,
      "mutationCount": 1
  }
}

For more information and examples, refer to INSERT.

Inserting Documents in Bulk

To insert several documents at once, use multiple VALUES clauses. The VALUES keyword itself is optional in the second and later iterations of the clause.

The following query creates two documents in the airline keyspace.

Context

Set the query context to the inventory scope in the travel sample dataset. For more information, see Setting the Query Context.

Query

INSERT INTO airline (KEY,VALUE)
VALUES ( "airline_4444",
    { "callsign": "MY-AIR",
      "country": "United States",
      "iata": "Z1",
      "icao": "AQZ",
      "name": "80-My Air",
      "id": "4444",
      "type": "airline"} ),
VALUES ( "airline_4445",
    { "callsign": "AIR-X",
      "country": "United States",
      "iata": "X1",
      "icao": "ARX",
      "name": "10-AirX",
      "id": "4445",
      "type": "airline"} )
RETURNING *;

Results

{
  "requestID": "2fabc03a-ea9b-49fd-a044-6ef667381311",
  "signature": {
      "*": "*"
  },
  "results": [
  {
      "airline": {
          "callsign": "MY-AIR",
          "country": "United States",
          "iata": "Z1",
          "icao": "AQZ",
          "id": "4444",
          "name": "80-My Air",
          "type": "airline"
      }
  },
  {
      "airline": {
          "callsign": "AIR-X",
          "country": "United States",
          "iata": "X1",
          "icao": "ARX",
          "id": "4445",
          "name": "10-AirX",
          "type": "airline"
      }
  }
  ],
  "status": "success",
  "metrics": {
      "elapsedTime": "5.7617ms",
      "executionTime": "5.4635ms",
      "resultCount": 2,
      "resultSize": 505,
      "serviceLoad": 4,
      "mutationCount": 2
  }
}

Inserting the Results of a Query

To insert documents using a query, use the INSERT statement with a SELECT statement.

Use the bracketed KEY keyword to specify the document key.
Use the optional VALUE keyword to specify the body of the document to insert. The body of the inserted document is usually based on the result returned by the SELECT statement.
Use the SELECT statement to return a resultset which is used as a basis for the inserted documents. The INSERT statement inserts a document for every result returned by the SELECT statement.
If required, use the RETURNING clause specifies what the query returns when the document is inserted.

The document key that you specify must be unique for every document that you insert. For example, you can use the UUID() function to generate a unique key for each document.

The following query creates a copy in the airport keyspace of any document whose airportname is "Heathrow".

Context

Set the query context to the inventory scope in the travel sample dataset. For more information, see Setting the Query Context.

Query

INSERT INTO airport (KEY UUID(), VALUE _airport)
    SELECT _airport FROM airport _airport
    WHERE airportname = "Heathrow"
RETURNING *;

Results

[
  {
    "airport": {
      "airportname": "Heathrow",
      "city": "London",
      "country": "United Kingdom",
      "faa": "LHR",
      "geo": {
        "alt": 83,
        "lat": 51.4775,
        "lon": -0.461389
      },
      "icao": "EGLL",
      "id": 507,
      "type": "airport",
      "tz": "Europe/London"
    }
  }
]

Replacing Existing Documents

The INSERT statement fails if a document with the same document key already exists in the keyspace.

To insert documents into a keyspace and replace any existing documents with the same key, use the UPSERT statement. This has the same syntax as the INSERT statement.

The following query creates two documents in the landmark keyspace. If documents with the same keys already exist, the existing documents are replaced.

Context

Set the query context to the inventory scope in the travel sample dataset. For more information, see Setting the Query Context.

Query

UPSERT INTO landmark (KEY, VALUE)
VALUES ("upsert-1", { "name": "The Minster Inn", "type": "landmark-pub"}),
("upsert-2", {"name": "The Black Swan", "type": "landmark-pub"})
RETURNING VALUE name;

Results

[
  "The Minster Inn",
  "The Black Swan"
]

For more information and examples, refer to UPSERT.

Merging and Inserting Documents

You can also insert documents by merging: that is, by joining one data source to another, and inserting documents any documents that do not match.

To insert documents using a merge, use the MERGE statement with the INSERT action:

Use the INTO keyword to specify the target data source. This is the data source into which documents will be inserted.
Use the USING keyword to specify the source. This is the data source to check against the target.
Use the ON keyword to specify the merge predicate. This is a condition that must be met to match an object in the source with an object in the target.
Use WHEN NOT MATCHED THEN INSERT to specify that when a document in the source does not match a document in the target, the document should be inserted in the target.
1. If necessary, use the bracketed KEY keyword to specify the document key.
2. If necessary, use the bracketed VALUE keyword to specify the body of the document to insert.
3. If necessary, use the WHERE clause to specify any further conditions that must be met for documents to be inserted.
If required, use the LIMIT clause to specify the greatest number of documents that may be inserted.
If required, use the RETURNING clause to specify what should be returned when the documents are inserted.

The following query compares a source set of airport data with the target airport keyspace. If the airport already exists in the airport keyspace, the record is updated. If the airport does not exist in the airport keyspace, a new record is inserted.

Context

Set the query context to the inventory scope in the travel sample dataset. For more information, see Setting the Query Context.

Query

MERGE INTO airport AS target
USING [
  {"iata":"DSA", "name": "Doncaster Sheffield Airport"},
  {"iata":"VLY", "name": "Anglesey Airport / Maes Awyr Môn"}
] AS source
ON target.faa = source.iata
WHEN MATCHED THEN
  UPDATE SET target.old_name = target.airportname,
             target.airportname = source.name,
             target.updated = true
WHEN NOT MATCHED THEN
  INSERT (KEY UUID(),
          VALUE {"faa": source.iata,
                 "airportname": source.name,
                 "type": "airport",
                 "inserted": true} )
RETURNING *;

Results

[
  {
    "target": {
      "airportname": "Anglesey Airport / Maes Awyr Môn",
      "faa": "VLY",
      "inserted": true,
      "type": "airport"
    }
  },
  {
    "source": {
      "iata": "DSA",
      "name": "Doncaster Sheffield Airport"
    },
    "target": {
      "airportname": "Doncaster Sheffield Airport",
      "city": "Doncaster, Sheffield",
      "country": "United Kingdom",
      "faa": "DSA",
      "geo": {
        "alt": 55,
        "lat": 53.474722,
        "lon": -1.004444
      },
      "icao": "EGCN",
      "id": 5562,
      "old_name": "Robin Hood Doncaster Sheffield Airport",
      "type": "airport",
      "tz": "Europe/London",
      "updated": true
    }
  }
]

For more information and examples, refer to MERGE.

Reference and explanation:

Querying with SDKs:

C | .NET | Go | Java | Node.js | PHP | Python | Ruby | Scala

Insert Data with a Query

Introduction

Inserting a Document

Inserting Documents in Bulk

Inserting the Results of a Query

Replacing Existing Documents

Merging and Inserting Documents

Related Links