Solved

Temporary table visible only to the connection that created it

Posted on 2003-12-08
7
1,727 Views
Last Modified: 2008-02-26
I HEARD the noise but don't know where it came from.....

Is it possible to create a table in MSSQL that will be visible only to the connection that created it? What I want is a place to store information and manipulate it with the SQL language but I need to protect it so that I can have several copies running at the same time and each copy visible only to its client.

I can achieve it using permanent tables but this possibility is exactly what I need....

So if you can assure me that I can't do it, it will still be a valid answer....
0
Comment
Question by:rmaranhao
  • 3
  • 3
7 Comments
 
LVL 4

Expert Comment

by:szacks
ID: 9897904
use a # sign in front of the table and it will only be available to the connection that created it.

example: create table #table1(F1 int, F2 int)

then you would use normal syntax with it. select * from #table1 you can even add indexes and stuff.
0
 
LVL 2

Expert Comment

by:poliguin
ID: 9897912
use a table variable

syntax:

DECLARE @MyTable Table(AnID INT, AValue VARCHAR(255))

INSERT INTO @MyTable SELECT ........


SELECT AnID, AVALUE FROM @MyTable
0
 
LVL 2

Expert Comment

by:poliguin
ID: 9897930
a note,

using CREATE #MyTable(....) will create a table that is accessible by the database.  if someone connects to the while someone is still in the stored procedure, you will get an error that states a table of name #MyTable already exists.  using a table variable allows you to not worry about that table already existing, for it's lifespan is unique to the execution of the stored procedure and many people can fire the same sp with a table variable at the same time or within the same execution time.  you can't do so with CREATE TABLE #MyTable(...).  a table variable works just like a regular table too.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 4

Accepted Solution

by:
szacks earned 125 total points
ID: 9898106
poliguin,

You are incorrect. If you open Query Analyzer for example and type create table #table1  and then open a second query window in QA and type create table #table1 it will create 2 tables that have no relationship to one another.
This is true in stored procedures as well, it's just very easy to test it in QA.
The way that # tables are handled by the database is they are created in the tempdb with somesort of unique identifier attached to the table name which is automatically handled by the engine wqhen you use the table name.
0
 
LVL 5

Author Comment

by:rmaranhao
ID: 9898387
szacks, you are perfecly correct.

Just one more doubt: I the connection is lost, how do I drop this table?
0
 
LVL 2

Expert Comment

by:poliguin
ID: 9898484
i stand corrected on that, i genearlly always run the same instance of qa, so never really noticed that before.


notes about usage of table variables (As taken from books online)
Use table variables instead of temporary tables, whenever possible. table variables provide the following benefits:

A table variable behaves like a local variable. It has a well-defined scope, which is the function, stored procedure, or batch in which it is declared.
Within its scope, a table variable may be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, table may not be used in the following statements:

INSERT INTO table_variable EXEC stored_procedure

SELECT select_list INTO table_variable statements.

table variables are cleaned up automatically at the end of the function, stored procedure, or batch in which they are defined.

table variables used in stored procedures result in fewer recompilations of the stored procedures than when temporary tables are used.


Transactions involving table variables last only for the duration of an update on the table variable. Thus, table variables require less locking and logging resources.
Assignment operation between table variables is not supported. In addition, because table variables have limited scope and are not part of the persistent database, they are not impacted by transaction rollbacks.
0
 
LVL 4

Expert Comment

by:szacks
ID: 9898609
Thank you for posting that comment on the usage of table variables. I have never used them before, but it seems like they are worth looking into.

One of the beauties of temporary tables is that the database deals with the clean up for you. You do not have to drop a temporary table, though it is considered good form to do so.
The tempdb is specifically made to handle things like this.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Copying MS SQL database does not copy default balues or identity specificatio 3 49
SQL Login 17 38
ASP.NET 5 Templates 2 65
Extract string portion 2 12
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now