How to use temporary SQL Server tables and table Variables

SQL Server Temporary tables of are divided into local temporary tables and global temporary tables, which are described as follows in books online:

There are two types of temporary tables: local and global, both of which have different names, visibility, and availability. The name of the local temporary table starts with a single numeric sign (#). They are only visible to the current user connection and are deleted when the user is disconnected from the Microsoft SQL Server 2000 instance. The name of the global temporary table starts with a mathematical sign (#). After being created, it is visible to all users. All users who reference this table are deleted when they are disconnected from SQL Server.

The local temporary table created in the stored procedure is equivalent to the stored procedure variable. It is visible only in the stored procedure, andProgramThe created local temporary table is the same as that written in the Microsoft online series and is visible to the current user connection. With this understanding, you can use different methods to create a local temporary table under different situations and requirements. This will make the temporary table the most useful. The SQL code:CodeRun later! ">

Normal browsing Copy code Print Code

4. /***************** Table variables ********************/
6. Declare@ TTable(
8. CataidInt
10. )
14. Insert Into@ TSelectA. cataidFromF_getchlid (27,'123')
16. Select Top10 favorites. *, multicata. cataname
18. FromFavoritesLeft Outer JoinMulticataOnFavorites. cataid = multicata. cataid
20. WhereFavorites. userid ='123' AndFavorites. cataidIn(
22. Select*From@ T)
28. /**************** Temporary table ********************* ***/
30. Method 1,
32. SelectA. cataidInto# TFromF_getchlid (27,'123')
34. Select Top10 favorites. *, multicata. cataname
36. FromFavoritesLeft Outer JoinMulticataOnFavorites. cataid = multicata. cataid
38. WhereFavorites. userid ='123' AndFavorites. cataidIn(
40. Select*From# T)
42. Drop Table# T
48. Method 2,
50. Create Table# T (
52. CataidInt
54. )
56. Insert Into# TSelectA. cataidFromZhaoguoguang. f_getchlid (27,'123')
58. Select Top10 favorites. *, multicata. cataname
60. FromFavoritesLeft Outer JoinMulticataOnFavorites. cataid = multicata. cataid
62. WhereFavorites. userid ='123' AndFavorites. cataidIn(
64. Select*From# T)
66. Drop Table# T