How can I insert a row using other rows data?

[talongao]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from typing import Optional

from sqlmodel import Field, SQLModel, create_engine

class Test(SQLModel, table=True):
    total:int = Field(default=0)
    count:int = Field(default=0)

Description

How can I get this SQL using SQLModel?

INSERT INTO test (total, count) 
VALUES ((SELECT total FROM test ORDER BY id DESC LIMIT 1) + 3, 3);

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.19

Python Version

3.12.3

Additional Context

No response

[YuriiMotov]

Use insert(Target).from_select(...)

Runnable code example:

from sqlmodel import SQLModel, Field, create_engine, Session, select
from sqlalchemy import insert


# Define two tables with the same schema
class Source(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str


class Target(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str


# Create an in-memory SQLite database
engine = create_engine("sqlite:///:memory:")
SQLModel.metadata.create_all(engine)

# Insert some sample rows into Source
with Session(engine) as session:
    session.add_all([Source(name="Alice"), Source(name="Bob")])
    session.commit()

# Copy rows from Source → Target in one SQL query
stmt = insert(Target).from_select(
    [Target.name],   # target columns
    select(Source.name)  # source columns
)

with Session(engine) as session:
    session.exec(stmt)
    session.commit()

# Check copied rows
with Session(engine) as session:
    results = session.exec(select(Target)).all()
    print(results)  # [Target(id=1, name='Alice'), Target(id=2, name='Bob')]