insertFromSelect method

Future<void> insertFromSelect(BaseSelectStatement select, { required Map<Column<Object>, Expression<Object>> columns, InsertMode mode = InsertMode.insert, UpsertClause<T, D>? onConflict, })

Inserts rows from the select statement.

This method creates an INSERT INTO SELECT statement in SQL which will insert a row into this table for each row returned by the select statement.

The columns map describes which column from the select statement should be written into which column of the table. The keys of the map are the target column, and values are expressions added to the select statement.

For an example, see the documentation website

Implementation

Future<void> insertFromSelect(
  BaseSelectStatement select, {
  required Map<Column, Expression> columns,
  InsertMode mode = InsertMode.insert,
  UpsertClause<T, D>? onConflict,
}) async {
  // To be able to reference columns by names instead of by their index like
  // normally done with `INSERT INTO SELECT`, we use a CTE. The final SQL
  // statement will look like this:
  // WITH source AS $select INSERT INTO $table (...) SELECT ... FROM source
  final ctx = GenerationContext.fromDb(database);
  const sourceCte = '_source';

  ctx.buffer.write('WITH $sourceCte AS (');
  select.writeInto(ctx);
  ctx.buffer.write(') ');

  final columnNameToSelectColumnName = <String, String>{};
  columns.forEach((key, value) {
    final name = select._nameForColumn(value);
    if (name == null) {
      throw ArgumentError.value(
          value,
          'column',
          'This column passd to insertFromSelect() was not added to the '
              'source select statement.');
    }

    columnNameToSelectColumnName[key.name] = name;
  });

  mode.writeInto(ctx);
  ctx.buffer
    ..write(' INTO ${ctx.identifier(table.aliasedName)} (')
    ..write(columnNameToSelectColumnName.keys.map(ctx.identifier).join(', '))
    ..write(') SELECT ')
    ..write(
        columnNameToSelectColumnName.values.map(ctx.identifier).join(', '))
    ..write(' FROM $sourceCte');
  if (onConflict != null) {
    // Resolve parsing ambiguity (a `ON` from the conflict clause could also
    // be parsed as a join).
    ctx.buffer.write(' WHERE TRUE');
    _writeOnConflict(ctx, mode, null, onConflict);
  }

  return await database.withCurrentExecutor((e) async {
    await e.runInsert(ctx.sql, ctx.boundVariables);
    database
        .notifyUpdates({TableUpdate.onTable(table, kind: UpdateKind.insert)});
  });
}