Solved

How can you check if a Global Temporary table exists ?

Posted on 2004-08-08
7
527 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
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL 2014 always on 31 58
Dynamic SQL select query 4 37
Need help in debugging a UDF results 7 22
job schedule 8 18
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

856 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