How to create a Global Temp Table**

Question

How to create a Global Temp Table**

Kaushik_rao

Newbie

Points: 6
More actions
October 9, 2009 at 8:37 am

#142408

How to create a Global Temp Table in which the user should be able to select the values inserted by him only

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323547 More actions · Answer 1

well first, why a global temp table if you want to select justthe stuff your user inserted? why would a private temp table not do the trick?

but to answer your question, here's how i would do it:

if not exists (select object_id('tempdb..##MyGlobalTemp'))

create table ##MyGlobalTemp(spid int,otherdata varchar(30) )

insert into ##MyGlobalTemp(spid,otherdata)

select @@spid,'stuff'

select * from ##MyGlobalTemp where spid=@@spid

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Kaushik_rao Newbie Points: 6 More actions · Answer 2

Still the records inserted in User Session 1 is visible in the user Session2, guess, it is the behavior of Global temporary table. I need something similiar to Global Temporary table in Oracle. The Requirement goes like this, the users insert multiple records at runtime, on doing a Select Top 1* , it should retrieve only the respective first record. The table should be physically available. How can I achieve it in MSSQL

Lowell SSC Guru Points: 323547 More actions · Answer 3

from what i read here:http://www.oracle-base.com/articles/8i/TemporaryTables.php

an oracle global temporary table seems to be the same as a regualr sql temp table...it is created for the user who connected, is not accessible by anyone else, and goes away when the user disconnects. it seems that oracle just makes one table for multiple session to use, but still filters by session id.

that is different than a SQL global temp table, which exists for multiple sessions to access, but each session has access to all the data in the table.

since it sounds like your user needs to insert some adta, and then select one row based on some criteria, and also not mix it with other users data, you need just a simply temp table:

create table #mytemp...

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!