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
Solved

SQL Global Temp Table

Posted on 2013-01-03
4
730 Views
Last Modified: 2013-01-03
if exists (select * from sys.tables where name = N'##Temp02')
drop table ##temp02;    

This T-SQL is not working.  The table ##temp02 is not being dropped?
0
Comment
Question by:dastaub
  • 2
4 Comments
 
LVL 15

Accepted Solution

by:
Anuj earned 125 total points
ID: 38742856
You should check in tempdb

if exists (select * from tempdb.sys.tables where name = N'##Temp02')
drop table ##temp02;
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 125 total points
ID: 38742863
This will do..

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..##Temp02'))
DROP TABLE ##temp02;
0
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 250 total points
ID: 38742867
Local temporary tables are destroyed when you close your connection to SQL Server. There is no need to manually purge them under normal circumstances. If you maintain a persistent connection, or connection pooling, you may want to get in the habit of dropping temporary tables immediately after use.

Global temporary tables, on the other hand, since they are visible to all users in a given database, are destroyed along with the last connection which references them
0
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 250 total points
ID: 38742869
try this

IF OBJECT_ID('tempdb..##Temp02', 'U') IS NOT NULL
   drop TABLE ##Temp02

refer

http://stackoverflow.com/questions/1579363/global-temporary-table-delete-operation
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
tempdb log keep growing 7 32
Row insertion failed. Array 5 45
sql2016-WIn10: standard,for SQL servc-account.. 51 26
SQLCMD Syntax 2 13
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

791 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