Using temp tables in SQL Server with raw queries and transactions? #17330
Unanswered
truefleet
asked this question in
Help & Questions
Replies: 0 comments
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
I'm writing a script to grab/transform enrollment data from a spreadsheet report and store it in a SQL Server database. The data coming in numbers anywhere from 2500-5000 rows; I'm aware of the 1000-row limitation in inserts for SQL Server. My current plan is to do the following:
I'm trying to use raw query statements for this process. Each statement is provided a sequelize transaction object, and the transaction is committed at the end.
I'm getting an error that the temporary table is an invalid object/doesn't exist when I start the second step of the process (insertion).
I am assuming that since I'm using a transaction object, that the temporary table would remain throughout the process and drop after the call to commit. I'm wondering now if I don't fully understand how this is working/should work behind the scenes? Or if I'm missing something that needs to be added in order to get this going? Are temp tables even possible with sequelize and MSSQL? I've used sequelize and transactions plenty of times in the past but this is my first time attempting DDL statements in raw queries, and with temp tables to boot.
I stripped down my initial script as a test to see if that helps me determine what's going on. I eventually reworked the test code so that right now the first raw statement is both a CREATE and an INSERT statement, while the next statement should insert another row separately, and a final statement should count the number of entries from the previous statements.
This is the stripped-down code; I've included part of the
new sequelize()
call from the connection service so you can see how it's created.When run, this is the resulting output:
I see that the db connection is made and dropped before being made again, but I've determined that happens in any other project I ran a test on so I'm not focused on that at the moment. You'll see that the table is created and one row is inserted, but then the next statement, that is separate from the first two, fails because the table doesn't exist.
I have tried adding the schema name to the table variable, and also dropping the variable name entirely and hard-coding the table name for each statement, all with the same results. I have also tried creating the temp table directly from a model and creating it using
sync()
but the same thing happens.I'm not sure what else I might be missing. Any guidance for getting the temp table to be recognized throughout the transaction would be appreciated.
Beta Was this translation helpful? Give feedback.
All reactions