Solved

How can you check if a Global Temporary table exists ?

Posted on 2004-08-08
7
572 Views
Last Modified: 2008-01-09
How can you check if a Global Temporary table exists and if it doesnt - create this table.

You can't have multiple global temporary table with the same name - right?
(although multiple users can create local temporary tables with the same name...)
0
Comment
Question by:MargusLehiste
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 11

Accepted Solution

by:
ram2098 earned 150 total points
ID: 11750221
IF NOT EXISTS (SELECT * FROM TEMPDB.dbo.SYSOBJECTS WHERE NAME ='##TEMP_Global')
      CREATE  TABLE  ##TEMP_Global( BATCHNO VARCHAR(3), NOOFRECS int)


This statement creates the Global temp table only if not exists.
0
 
LVL 11

Expert Comment

by:ram2098
ID: 11750224
Also, you cannot have the multiple Global Temporary tables with the same name.
0
 
LVL 12

Expert Comment

by:kselvia
ID: 11750441
Or a little shorter:

If Object_ID('tempdb..##table_name') Is Null
  Create Table ##table_name (col1 int)

0
Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

 
LVL 1

Author Comment

by:MargusLehiste
ID: 11754049
kselvia - in 'tempdb..##table_name' what are those 2 dots about?
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 11754114
Or, a little more portable:

If Not Exists(Select * From TempDB.Information_Schema.Tables Where Table_Name = '##Table_Name')
  Create Table ##table_name(Col1.Int)

This is guaranteed to work in future versions as well.
0
 
LVL 12

Assisted Solution

by:kselvia
kselvia earned 100 total points
ID: 11754468
The full name of a table is dababase.owner.table.  If you do not specify a database or owner, the curret db and your username will be used. tempdb..##table means tempdb database, and your username.  Equivalent to tempdb.dbo.##table if you are logged in as dbo or equivalent.

Sjoerd, Object_ID() is not in danger of being dropped or replaced that I am aware of. Why do you suspect information_schema tables are more portable?
0
 
LVL 12

Expert Comment

by:kselvia
ID: 11756126
I think Sjoerd was refering to Ram2098's answer rathe than mine. Yes, if you need to reference sysobjects, it is better to reference information_schema tables.  Sysobjects may not be supported in the future.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

624 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