暫存資料表使用方法

 

在SQL SERVER2000中，建立暫存資料表方式有3種：

1）create table #table_name(field1 type,field2 type,..........)

     insert into #table_name values(..............)

     select * from #table_name

2）create table tempdb.table_name(field1 type,field2 type,..........)

     insert into #table_name values(..............)

     select * from #table_name

3)select * into #temp from (select * from Func) A

   select * from #temp

   drop table #temp

註：

（1）暫存資料表的特點為建立的暫存資料表由建立者使用，多個人可以同時運行該條語句，而不必擔心表名重複。當退出資料庫或事務被提交時，表自動刪除。建立的表需要手工刪除，和普通的表的區別是：把表放在了資料庫的一個臨時空間裡，如果不手工刪除，當資料庫重起時，資料庫管理系統會自動將其刪除。

（2）暫存資料表有兩種類型：本地和全域。它們在名稱、可見度以及可用性上有區別。本地暫存資料表的名稱以單個數字記號 (#) 打頭；它們僅對當前的使用者串連是可見的；當使用者從 SQL Server 執行個體中斷連線時被刪除。全域暫存資料表的名稱以兩個數字記號 (##) 打頭，建立後對任何使用者都是可見的，當所有引用該表的使用者從 SQL Server 中斷連線時被刪除。

（3）有另外一種暫存資料表，建立方式：

declare @table_name table (field1 type,field2 type,.......... )  

這種暫存資料表當語句結束時釋放暫存資料表一個會話可同時建立幾個相同名字的表，但不能在同一條語句中聲明幾個同名的暫存資料表。用法如下：

SET   QUOTED_IDENTIFIER   ON    
  GO  
  SET   ANSI_NULLS   ON    
  GO  
  create   proc   spGetTreeVar   (@ParentID   int   )  
  as  
  begin  
  set   nocount   on  
      /*如果不是SQLSERVER2000可以用暫存資料表*/  
  declare   @tmp1     table   (   ParentID   int   ,   ID   int   ,   isclass   int   )  
  declare   @tmp2     table   (   ParentID   int   ,   ID   int   ,   isclass   int   )  
  declare   @tmp3     table   (   ParentID   int   ,   ID   int   ,   isclass   int   )  
       
  insert   @tmp1   select   ParentID,ID   ,IsCls     from   Variables   where       ParentID   =   @ParentID   and   IsDelete   =   0  
  insert   @tmp3   select   ParentID,ID   ,IsCls     from   Variables   where       ParentID   =   @ParentID   and   IsDelete   =   0  
   
      /*迴圈的次數等於樹的深度*/  
  while   exists(select   *   from   @tmp1   where   isclass   =   1   )  
  begin  
  insert   @tmp2   select   a.ParentID,a.ID,a.IsCls     from   Variables   a,@tmp1   b   where       a.ParentID   =   b.ID   and   IsDelete   =   0  
          /*@tmp2表中存本次查詢的層次的所有結點*/  
  delete   from   @tmp1   where   IsClass   =   1    
          /*@tmp1表中最終存的是葉子結點*/  
  insert   @tmp1   select   *   from   @tmp2  
          /*@tmp3表中最儲存每次查到的子孫*/  
  insert   @tmp3   select   *   from   @tmp2  
          delete   from   @tmp2  
  end      
  select   Distinct   Variables.*   from   Variables   inner   join   @tmp1   b   on   Variables.ID   =   b.ID    
  set   nocount   off  
  end  
   
  GO  
  SET   QUOTED_IDENTIFIER   OFF    
  GO  
  SET   ANSI_NULLS   ON    
  GO  

判斷表明是否已存

判斷普通表：

if exists(select * from dbo.sysobjects where id = object_id(N'表名') and OBJECTPROPERTY(id,N'IsUserTable')=1)

print 'exists'

或

IF (OBJECT_ID('表名') IS not NULL)

print 'exists'

 

判斷暫存資料表：

if object_id('tempdb..表名') is not null

print 'exists'

或者：if object_id('表名') is not null

print 'exists'