Create a Temporary Table in SQL Server with Read only access [closed]-CodePudding

I am working on an SQL script at the moment. It'll probably take me forever as I'm not an expert at all. I have read only access to the DB where I am pulling the initial data from, however I want to create a #TempTable. When I try to do this it seems to create it (at least I don't get any errors) but when I try to view the contents of it I get an error saying I don't have permission to view.

I also don't seem to have the access to Drop Table - I'm guessing this is also due to Read Only status.

So I'm wondering is there a way around this? Can I have the 'temptable' stored locally on my PC or is there anything else I can do?

Thanks in advance, N

CodePudding user response：

A temp table is automatically deleted when you close a connection. If you are doing this work within a Stored Procedure or from SSMS then this doesn't affect you, but if you are doing this from .NET (or another programming language) and you are connecting/disconnecting, or loading it from one script and trying to read it from a report (for example), then no, the table won't be visible or exist for any session/connection other-than the one that created it.

If you need it for a few minutes, and you intend to connect/disconnect (more than zero times), you should create a table. You can do it under your own schema or pick a name that sounds temporary.

Ex. If I am connecting as Domain\DataUser1, my schema is [Domain\DataUser1]

SELECT UserName FROM LoggingTable
INTO TABLE [Domain\DataUser1].TempTable1
--I need those square brackets around my schema because of the \

This will create a table under my user's schema. I can query it from another connection

SELECT * FROM [Domain\DataUser1].TempTable1

Oh. Don't forget to drop the temp table when you are done:

DROP TABLE [Domain\DataUser1].TempTable1
-- or, if you intend to keep using it --
TRUNCATE TABLE [Domain\DataUser1].TempTable1