create global temporary tables in SQL SERVER

I m trying to create a global temporary table whose data will be deleted after the end of the session..

the DDL in oracle is

CREATE GLOBAL TEMPORARY TABLE STUDENT
(

name CHAR(20),

) ON COMMIT DELETE ROWS ;

I want to know the corresponding DDL in SQL Server..

when i try the following

CREATE TABLE ##STUDENT
(

name CHAR(20),

)

the table gets deleted at the end of the session..help needed

when there is no more connection referencing the

GTT it automatically destroys itself

|||

There is not such a thing. You can create a permanent table and include in it's key the process_id value (which you can get from @.@.spid). Then you just delete the data from the table when you get finished (and when you start in case you don't get things cleaned up because of some error)

On the other hand, is it so bad for what you are doing to just create the structure on each run? And note that ##tables are available to all other connections, #tables just to your session. You should use # tables unless there is some compelling reason to use global temp tables.