"SELECT INTO #temptable" is a SQL server specific trick that just creates #temptable on the fly. SQL standard is INSERT from SELECT. Since you're looking to dynamically create tmptbl, this implies that there needs to be a table() object generated given the set of columns from the original table. While "SELECT INTO" is a SQL server trick that creates the table on the fly, this doesn't help us because we don't get back a "tmptbl" object with columns. So there's not much point in using it.
So we go to the recipe I already gave you on the issue you posted: https://bitbucket.org/zzzeek/sqlalchemy/issues/4148/feature-request-mssql-select-into#comment-41933692 you'd like the columns to be dynamic. OK, here's that: from sqlalchemy import * from sqlalchemy.sql import quoted_name from sqlalchemy.orm import Session from sqlalchemy.ext.declarative import declarative_base def select_into(name, query): temp_table = Table( quoted_name(name, quote=False), MetaData(), *[Column(attr['name'], attr['type']) for attr in query.column_descriptions] ) conn = query.session.connection() temp_table.create(conn) conn.execute(temp_table.insert().from_select( [attr['name'] for attr in query.column_descriptions], query.statement )) return temp_table Base = declarative_base() class A(Base): __tablename__ = 'a' id = Column(Integer, primary_key=True) x = Column(Integer) y = Column(Integer) e = create_engine("mssql+pymssql://scott:tiger^5HHH@mssql2017/test", echo=True) Base.metadata.drop_all(e) Base.metadata.create_all(e) s = Session(e) s.add_all([A(x=1, y=2), A(x=2, y=3)]) s.commit() q = s.query(A.x, A.y) tmp_table = select_into("#temp", q) subqu1 = s.query(tmp_table).filter(tmp_table.c.x == A.x).exists() print s.query(A).filter(subqu1).all() output: CREATE TABLE #temp ( x INTEGER NULL, y INTEGER NULL ) INSERT INTO #temp (x, y) SELECT a.x, a.y FROM a SELECT a.id AS a_id, a.x AS a_x, a.y AS a_y FROM a WHERE EXISTS (SELECT 1 FROM #temp WHERE #temp.x = a.x) On Sat, Dec 16, 2017 at 3:47 AM, Евгений Рымарев <rymarev.eug...@gmail.com> wrote: > Hello! > How i can create something like that: > SELECT column > INTO #tmptbl > FROM table > > SELECT * > FROM table2 t2 > WHERE EXISTS(SELECT 1 > FROM #tmptbl > WHERE column = t2.column) > > in SQLAlchemy I imagine it like this: > tmptbl = session.query(table.c.column).into('#tmptbl') > subqu1 = session.query(tmptbl).filter(tmptbl.c.column == > table2.c.column).exists() > result = session.query(table2).filter(subqu1) > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
