日期:2014-5-18 浏览次数:20035次

请教临时表的问题
SQL code

    SELECT *   ,user_id
                    ,CAST(NULL AS nvarchar(100))    AS user_name
          ,CAST(NULL AS nvarchar(100))    AS login_name
          ,CAST(NULL AS nvarchar(100))    AS password
          ,CAST(NULL AS nvarchar(100))    AS department    
    INTO #TempTB
    FROM T

    --更新用户信息
    UPDATE #TempTB
    SET user_name = T_USER_User.user_name,
        login_name = T_USER_User.login_name,
        password = T_USER_User.password,
        department = T_USER_User.department
    FROM T_USER_User
    WHERE #TempTB.user_id = T_USER_User.auto_id




然后将这个临时表#TempTB和一些参数发给分页存储过程供系统调用,问题是我每次调用分页存储过程就得生成一次这个临时表#TempTB,事实上一个临时表是比较稳定的,不需要每次都重新生成,这样效率很慢,现在想把他放到视图中,这样就相当于常驻内存了,但是不知道怎么弄,虚心请教,谢谢


------解决方案--------------------
你创建一个试图啊
------解决方案--------------------
create view v_view 
as 
SELECT * ,user_id
,CAST(NULL AS nvarchar(100)) AS user_name
,CAST(NULL AS nvarchar(100)) AS login_name
,CAST(NULL AS nvarchar(100)) AS password
,CAST(NULL AS nvarchar(100)) AS department
INTO #TempTB
FROM T

update v_view 就好了啊

------解决方案--------------------
可以用全局临时表,即任何进程都可用的临时表.
------解决方案--------------------
视图不会常驻内存中吧

临时表挺好的
------解决方案--------------------
SQL code
SELECT *   ,user_id
                    ,CAST(NULL AS nvarchar(100))    AS user_name
          ,CAST(NULL AS nvarchar(100))    AS login_name
          ,CAST(NULL AS nvarchar(100))    AS password
          ,CAST(NULL AS nvarchar(100))    AS department    
    INTO ##TempTB  ----------
    FROM T