Delete the temp table

Temporary Tables

The simple answer is yes you can. Let look at a simple CREATE TABLE statement:

CREATE TABLE #Yaks (
YakID int,
YakName char(30) )

You'll notice I prefixed the table with a pound sign (#). This tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server. When I close this session, the table will be automatically dropped. You can treat this table just like any other table with a few exceptions. The only real major one is that you can't have foreign key constraints on a temporary table.

Temporary tables are created in TempDB. If you run this query:

CREATE TABLE #Yaks (
YakID int,
YakName char(30) )

select name
from tempdb..sysobjects 
where name like '#yak%'

drop table #yaks

You'll get something like this: 

name
------------------------------------------------------------------------------------ 
#Yaks_________________________ . . . ___________________________________00000000001D

(1 row(s) affected)

except that I took about fifty underscores out to make it readable. SQL Server stores the object with a some type of unique number appended on the end of the name. It does all this for you automatically. You just have to refer to #Yaks in your code.

If two different users both create a #Yaks table each will have their own copy of it. The exact same code will run properly on both connections. Any temporary table created inside a stored procedure is automatically dropped when the stored procedure finishes executing. If stored procedure A creates a temporary table and calls stored procedure B, then B will be able to use the temporary table that A created. It's generally considered good coding practice to explicitly drop every temporary table you create.  If you are running scripts through SQL Server Management Studio or Query Analyzer the temporary tables are kept until you explicitly drop them or until you close the session.

Now let's get back to your question. The best way to use a temporary table is to create it and then fill it with data. This goes something like this:

CREATE TABLE #TibetanYaks(
YakID int,
YakName char(30) )

INSERT INTO #TibetanYaks (YakID, YakName)
SELECT 	YakID, YakName
FROM 	dbo.Yaks
WHERE 	YakType = 'Tibetan'

-- Do some stuff with the table

drop table #TibetanYaks

And you can use the SQL as bellow when you create a temp table.

if object_id('TempDB..#TibetanYaks')  is not null
drop table #TibetanYaks

CREATE TABLE #TibetanYaks(
YakID int,
YakName char(30) )

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14321372/viewspace-610632/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14321372/viewspace-610632/