1

Using following code I have created a temp table in azure sql database.

CREATE TABLE ##UpsertTempTable (
  eno varchar(25),
  ename varchar(25)
);

and I am want to check the data using the below query

 select * from ##UpsertTempTable

Ideally it should run without any issue as in all of the azure documentation it works without any issues but unfortunately it is not working and giving below error.

I tried looking solution in all places in the internet but could not find any relevant documentation for this issue.

Error : Failed to execute query. Error: Invalid object name '##UpsertTempTable'.

DarkRob
  • 3,843
  • 1
  • 10
  • 27
jin
  • 11
  • 4
  • Strange, I tried your code, there is no error happened. My SSMS version is v18.1 – Leon Yue Jul 24 '19 at 10:13
  • I think it should work if both commands entered within the same proc? See https://stackoverflow.com/a/53198806/78522 – iDevlop Jul 24 '19 at 10:17
  • Maybe you can try `CREATE TABLE #UpsertTempTable ( eno varchar(25), ename varchar(25) );` and 'select * from #UpsertTempTable' , to check if the error happen again. – Leon Yue Jul 24 '19 at 10:23
  • @LeonYue I already tried that as well but that also did not work. Just FYI I asked my colleague to run this on on-promise sql data base where it worked fine but when I am running this is query editor in azure portal sql database, it is giving error. – jin Jul 24 '19 at 10:36
  • @PatrickHonorez just for the simplicity, I am not using any procedure. just simple command and it is giving the error. Initially I thought that in azure sql database some special permissions need to be granted to access through query editor but could not find anything.. – jin Jul 24 '19 at 10:40

1 Answers1

0

I tried in Query Editor(Preview) in Portal, and create temporary table code doesn't work. I both used ##UpsertTempTable and #UpsertTempTable.

For example, when we run the code, no error happens . enter image description here

When you run select * from ##UpsertTempTable, Query editor will gives the error: enter image description here

I also try with SSMS V17.9 and SSMS V18.1, everything is ok.

What I think is the query editor doesn't support create temporary table well.

I asked Azure Support and wait their replay, please wait my update.

Update:

Azure Support replied me:

"This is by design, the temp tables exists as long as the connection is open. The current way portal query editor is designed, the connection is killed resulting in temp table being deleted. "

enter image description here

Hope this helps.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23
  • okay I will wait for your response. just for your information, one more thing I found strange is if we create a table emp with the same table structure as ##UpsertTempTable and from emp table if I insert a record into ##UpsertTempTable using select * into ##UpsertTempTable from emp statement I get the following message "Query succeeded: Affected rows: 1" – jin Jul 25 '19 at 07:49
  • @jin yeah, I tested the same operation with you, I think query editor doesn't know something like ` ##UpsertTempTable` or '#UpsertTempTable'. – Leon Yue Jul 26 '19 at 00:59
  • 1
    HI @jinj, Azure Support replied me. That is by design. If you want to create temporary table, we suggest you using SSMS or other workaround. I updated my answer. If my answer is helpful for you, you can accept it as answer( click on the check mark beside the answer to toggle it from greyed out to filled in.). This can be beneficial to other community members. Thank you. – Leon Yue Jul 29 '19 at 01:43
  • Thanks a lot for your help. I have clicked on the check mark but seems who has less then 15 reputations it does not show publicly. – jin Aug 05 '19 at 10:53
  • tried again to mark as answer and getting the following message - " Thanks for the feedback! Votes cast by those with less than 15 reputation are recorded, but do not change the publicly displayed post score." – jin Aug 06 '19 at 10:22