# Insert

> Ends a query chain by creating one row of ANOTHER entity per row the chain selects — an INSERT … SELECT in one statement. The chain is the source; the `new T { … }` inside the terminal names the target and its properties, and each value may read the source row. Returns how many rows were created.

<!-- id: query-insert-from · area: query · stability: stable · html: https://osysharp.com/reference/query/insert-from/ -->

## Summary        {#summary}
`.Insert(…)` closes the other load-and-loop pattern — *create a row per matching parent*. The chain selects the
SOURCE rows; the projection inside the terminal builds one TARGET row from each, in the database, in one
statement. The answer is how many rows were created.

## Signature      {#signature}
```osy syntax
<Source>.Where(s => …).Insert(s => new <Target> { Property = s.Property, Other = literal, Ref = s })   →   int
<list>.Insert(x => new <Target> { Property = x.Property, … })                                            →   int
```

## Description    {#description}

### One row per matching row   {#the-shape}
The canonical use grants something to everyone who lacks it — the **not-exists idiom**, which also makes the
statement re-runnable (already-covered rows simply do not match):

```osy title="a grant for every active member that has none" test app=query-insert-from
entity Member {
  [Required] string Email;
  bool Active;
}
entity Grant {
  [Required] Member Grantee;
  [Required] string Level;
}

int GrantAll() {
  return Member.Where(m => m.Active && !Grant.Any(g => g.Grantee == m))
               .Insert(m => new Grant { Grantee = m, Level = "Member" });
}
```

`Grantee = m` assigns the source ROW to a reference — each created row points at its own source. Any source
property can feed a target property (`Label = m.Email`), values may be captured locals or literals, may read
through the source row's references, and may embed a correlated scalar read — the same value rules as
[`.Update(…)`](https://osysharp.com/reference/query/update/), including the answer-for-absence refusal on a hop through a nullable reference.
Only a **row-returning** query is refused: a projection assigns one scalar per column.

### What must the projection set?   {#required}
Every **required** target property, and every property whose default is a per-row **expression** — and the compiler
says so, naming the field, before anything runs. That is required-by-default arriving EARLIER than it does for a
per-row `new`, because the projection is statically known. A property with a literal default (`string Status =
"New";`) may be omitted and gets its default, exactly as a per-row create would give it.

### Which rows does it read, and may it create?   {#security}
The source chain is the caller's ordinary secured read. The target's `allow create when` is a caller-level gate,
checked once — a caller who may not create these rows gets a refusal, not a smaller set. An `allow create where`
(the with-check on the written row) is verified over the created rows inside the same transaction: one violating
row rolls the whole statement back.

### What about collisions?   {#unique}
Two answers, and both are good ones. Without more, a `[Unique]` collision throws for the whole statement —
all-or-nothing, carrying the message your `[Unique]` declares — and the not-exists predicate above avoids minting
the duplicate at all. Or say what a collision MEANS with `onConflict:` — the **upsert**:

```osy title="insert new rows, merge colliding ones — safely re-runnable" test app=query-insert-from
entity Staged { [Required] string Sku; int Qty; bool Ready; }
entity Product {
  [Unique] string Sku;
  decimal Price;
  int Stock;
}

int Sync() =>
  Staged.Where(s => s.Ready)
        .Insert(s => new Product { Sku = s.Sku, Price = 10m, Stock = s.Qty },
                onConflict: (p, inc) => {
                  p.Price = inc.Price;              // take the incoming value
                  p.Stock = p.Stock + inc.Stock;    // or combine with what is already there
                });
```

`p` is the EXISTING row; `inc` is the row that **would have been inserted**, carrying the projected values — the
only shape a conflict can see. The collision key is inferred from the target's own `[Unique]` declaration (stated
once, at the model), the merge may not move the row off its key, and the merge half is judged as the UPDATE it is —
your `allow update` rules, per assigned property. Merged rows are audited as updates, inserted ones as creates.

### From a local list   {#from-a-list}
The source need not be stored. A list or array built in the body — of class instances, or of plain scalars — is a
source too, with the SAME verb, the same required-field checks at compile, the same stamps, defaults, security and
audit per row, and the same `onConflict:`. The projection runs per element in your function; the rows go to the
database as one statement (a very long list goes in several, inside one transaction — a failure anywhere leaves
nothing). This is the seeding shape, and the import shape:

```osy title="seed rows from a list — one statement, not one create per element" test app=query-insert-from
class Draft { public string Name; public int Weight; }
entity Tag { [Required] string Name; int Weight; string Status = "New"; }

int Seed() {
  var drafts = new List<Draft> {
    new Draft { Name = "alpha", Weight = 1 },
    new Draft { Name = "beta", Weight = 2 },
  };
  return drafts.Insert(d => new Tag { Name = d.Name, Weight = d.Weight * 10 });
}
```

A scalar list is the same thing with the element itself as the value:

```osy title="a name per element, merging any that already exist" test app=query-insert-from
entity Label { [Unique] string Name; int Seen; }

int Mark(List<string> names) =>
  names.Insert(n => new Label { Name = n, Seen = 1 },
               onConflict: (l, inc) => { l.Seen = l.Seen + 1; });
```

### When does it run?   {#immediacy}
Immediately, at the call — like its two siblings, and with the same refusal while your unit of work holds
uncommitted changes of the SOURCE type.

## Examples       {#examples}
```osy title="one audit-shaped row per closed order" test app=query-insert-from
entity Order {
  [Required] string Status;
  decimal Total;
}
entity Settlement {
  [Required] string Kind;
  decimal Amount;
}

int Settle() {
  return Order.Where(o => o.Status == "Closed")
              .Insert(o => new Settlement { Kind = "order", Amount = o.Total });
}
```

## See also       {#see-also}
- [Update](https://osysharp.com/reference/query/update/) · [Delete](https://osysharp.com/reference/query/delete/) — the other two bulk terminals, same security story
- [Where / Single / Count](https://osysharp.com/reference/query/where/) — selecting the source set
- [security { }](https://osysharp.com/reference/security/entity-security/) — `allow create when` / `allow create where`