Solved

How can you check if a Global Temporary table exists ?

Posted on 2004-08-08
7
548 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
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.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Syntax to get a value associated with most recent date 16 64
CASE Statement using different fields 8 33
SQL Query 9 29
Need split for SQL data 7 48
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

739 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