[MSSQL] SELECT * INTO #tmptbl FROM table

[Евгений Рымарев]

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)




[Mike Bayer]

"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)

> --
> 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+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Mike Bayer]

there are also ways to create exactly the "SELECT INTO" thing if you
really want that, it would involve creating a custom construct using
the API described at
http://docs.sqlalchemy.org/en/latest/core/compiler.html. However it
would still look a lot like the above because we still need to build a
table-like object with Column objects on it, if you want to continue
using that temp table as a SQLAlchemy expression language object.

Also of course all of this SQL can be sent just as a straight string
with session.execute() if you just need to do it once.