0

I am trying to run a raw T-SQL query that writes tables in the database. I experimented with both SELECT INTO and CREATE AS but both failed. The query containing SELECT INTO executes without errors but doesn't do anything, whereas the query with CREATE TABLE AS returns a Incorrect Syntax error. Does anyone know if this operation is possible using sqlalchemy (pyodbc) or a workaround?

query = 'select top 10 * into dbo.test_table from dbo.main_table'
# or
query2 = 'create table dbo.test_table from (select top 10 * from dbo.main_table)'

engine = create_engine(....)
conn = engine.raw_connection()
cursor = conn.cursor()
result = cursor.execute(query)
crabnebul
  • 124
  • 1
  • 7
  • 1
    "The query containing SELECT INTO executes without errors but doesn't do anything" - That could just be that you are not calling `cursor.commit()` after `cursor.execute()` – Gord Thompson Dec 22 '22 at 18:24

1 Answers1

-2

First of all, if cursors are not already an established pattern in your code, you may want to look at the example for Engine.begin() for a way to avoid them. Last I checked, in SQL Server, cursors are bad for performance.

Second, I would make sure your queries actually succeed when run from SQL Server tooling. I can't recreate your example, but based on here and here, your queries should be getting passed to the database directly, without being touched at all by SQL Alchemy.

Third, this answer to a previous question has an outline of SELECT INTO at various versions of SQLAlchemy.

xenrelay
  • 111
  • 1
  • 9