sql server 将结果插入临时表 sqlserver临时表详解

有时需要把数据临时保存到表中， 而且在某些情况下， 可能并不想使用永久性的表。例如，假设需要让数据只对当前会话可见，或者只对当前批处理可见。 另一个场景是， 假设想让数据对所有用户都可见， 允许他们看到完整的DDL和进行所有DML访问， 但却没有在任何用户数据库中创建表的权限。在这种情况下， 使用临时表可能会更方便。 SQL Server支持三种类型的临时表： 局部临 时表、 全局临时表及表变量。不论会话的数据库上下文如何，临时表都被创建在tempdb中。

一.局部临时表

要创建局部临时表，只需要在命名时以单个数字符号（＃）作为前缀，例如#T1。

局部临时表只对创建它的会话在创建级和调用堆栈内部级（内部的过程、函数、触发器、 以及动态批处理）是可见的。当创建级例程弹出调用堆栈，SQL Server就会自动删除相应的临时表。例如，假设一个存储过程Procl调用了另一个存储过程Proc2,而Proc2又调用了另一个存储过程Proc3,Proc3又调用了存储过程Proc4。Proc2在调用Proc3之前 创建了一个临时表#Tl。这时表#T1对Proc2、Proc3、以及Proc4是可见的，而对Procl1是不可见的，当Proc2完成时，SQL Server就会自动删除这个临时表。如果临时表是在 会话最外层嵌套的一个特殊的批处理(@@NESTLEVEL函数的返回值是O)中创建的， 则这个表对所有随后的批处理也是可见的，只有当创建会话从SQL Server实例断开时， SQL Server才会自动删除它。

可能会疑惑，当两个会话创建的局部临时表具有相同的名称时，SQL Server怎么避免名 称冲突。SQL Server内部会为临时表名称增加一个后缀，使表名称在tempdb数据库中保持惟一。

 

二.局部临时表

如果创建的是全局临时表则它对其他所有会话都可见。当创建临时表的会话断开数据库的联接，而且也没有活动在引用全局临时表时，SQL Server会自动删除相应的全局临时表。要创建全局临时表，只需要在命名时用两个数字符号(##)作为前缀，如##T1。

当需要和所有人共享临时数据时，就可以用全局临时表。访问全局临时表不需要任何特殊的权限，所有人都可以获取完整的DDL和DML访问。当然，每个人都可以完全访问也意味着任何人都可以删除这个表，所以也应该谨慎考虑全局临时表的副作用。例如，以下代码创建了一个全局临时表##Globals，它包含id和val两个列：

CREATE TABLE dbo. ##Globals
(
    id sysname NOT NULL PRIMARY KEY,
    val SQL_VARIANT NOT NULL
)

这个表在这里是想模仿全局变量(SQL Server不支持全局变量）。列id的数据类型是 sysname (SQL Server在内部用这个类型来代表标识符），列val的数据类型是SQL_VARIANT (一种通用的类型，差不多可以保存任何基础类型的值）。

 

三.表变量

表变量和局部临时表在某些方面有相同之处，也有不同之处。声明表变量的方式和声明其他变量类似，使用的都是DECLARE语句。
和使用局部临时表一样，表变量在tempdb数据库中也有对应的表作为其物理表示，而不是像通常所理解的那样，以为表变量只在内存中存在。和局部临时表类似，表变量也只对创建它的会话可见，但允许访问的范围更有限，它只对当前批处理可见。表变矗对调用堆栈中当前批处理的内部批处理是不可见的，对会话中随后的批处理也是不可见的。

如果回滚一个显式事务，在事务中对临时表所做的更改也将回滚；不过，通过语句对表量进行的更改， 如果在事务中已经完成了， 则不会被回滚。 只有通过活动语句进行的更改， 而且操作失败或在完成之前被终止了， 这样的更改才会被撤消。

临时表和表变量在性能优化方面也有区别，从性能上考虑， 对于少量的数据（只有几行），使用表变量更有意义， 否则，应该使用临时表。

 

四.表类型

SQL Server 2008引入了对表类型的支持。 通过创建表类型， 可以把表的定义保存到数据库中， 以后在定义表变量 、存储过程和用户定义函数的输入参数时，可以将表类型作为表的定义而重用。以下代码在数据库中创建了一个表类型 dbo.OrderTotalsByYear:

IF TYPE_ID('dbo.OrderTotalsByYear') IS NOT NULL 
    DROP TYPE dbo.OrderTotalsByYear;
CREATE TYPE dbo.OrderTotalsByYear AS TABLE
(    
    orderyear INT NOT NULL PRIMARY KEY, 
    qty INT NOT NULL 
)

创建好表类型以后， 每当需要根据表类型的定义来声明表变量时， 就不需要重复表定义代码， 只要简单的将变量的类型指定为dbo.OrderTotalsByYear就可以， 如下所示：

DECLARE @MyorderTotalssyvear AS dbo.OrderTotalsByYear;