Insert

Table of Contents

Overview

Field list and values
Field: value pairs
Insert from a select

See also
Related

INSERT creates new documents in a schema. The schema must already exist. Documents in a schema do not need to have the same fields.

Field list and values

Inserts a single document.

INSERT INTO WordList:Language (Id, Name)
VALUES (10, 'Italian')

Inserts several documents. Repeat as many value sets as you like.

INSERT INTO WordList:Language (Id, Name)
VALUES (11, 'Dutch'), (12, 'Finnish'), (13, 'Latin')

Values can be expressions.

INSERT INTO WordList:Language (Id, Name, Code)
VALUES (14, 'Swedish', Sha1('Swedish') + '-' + Guid())

Field: value pairs

Each document lists its own fields, so the fields can vary from document to document.

INSERT INTO WordList:Language
(Id: 15, Name: 'Polish'),
(Id: 16, Name: 'Czech', Code: Guid())

Insert from a select

Inserts the results of a query.

INSERT INTO WordList:Translation (SourceWord, SourceLanguage, TargetWord, TargetLanguage)
SELECT
    sw.Text as SourceWord,
    sl.Name as SourceLanguage,
    tw.Text as TargetWord,
    tl.Name as TargetLanguage
FROM
    WordList:Word as sw
INNER JOIN WordList:Language as sl
    ON sl.Id = sw.LanguageId
INNER JOIN WordList:Synonym as S
    ON S.SourceWordId = sw.Id
INNER JOIN WordList:Word as tw
    ON tw.Id = S.TargetWordId
INNER JOIN WordList:Language as tl
    ON tl.Id = tw.LanguageId
WHERE
    sw.Text = 'Cat'

Unique keys

Inserting a value that already exists in a unique key fails with a duplicate key violation and the whole statement is rolled back. See Create Index.

Bulk loading

Applications loading many documents should use Document.StoreMany in the client API, which stores a batch of documents in one round trip.