1.先使用判断: if object_id('tempdb..#tmp') Is Not Null Drop Table #tmp2.要是存储过程A中,含有调用存储过程B,而且存储过程A,B都有创建一个相同的临时表如#tmp,那么在运行时一定报错的。因此要避免这样相同的临时表名称。3.还有编译过程报错的一种情况: create proc up_test ( @typeid int ) As if object_id('tempdb..#tmp') Is Not Null Drop Table #tmpIf @typeid=1 Begin Select * Into #tmp From test1 End Else If @typeid=1 Begin Select * Into #tmp From test2 End Go 当点执行的时候,会报错:“数据库中已存在名为 '#tmp' 的对象。”,这样也要考虑使用不同的临时表名可以解决。
临时表基本都可以用CTE代替的
临时表与一般的表不同。它是保存到tempDb表中,临时表的表名与你所建的表名也不一样,因为他要为不同人的相同操作创建不同的临时表。表名存储时的格式:dbo.#tempTable_________________________________________________________00000000000Ddbo.#tempTable_________________________________________________________00000000000Edbo.#tempTable_________________________________________________________00000000000Fdbo.#tempTable_________________________________________________________000000000010通过上面你会看出它的命名规则,这个规则是16进制的。因为临时表的存储位置不同,名称是变化的,所以它的删除与一般表的删除语句是不一样的,下面的删除操作都是错误的:–错误的临时表删除操作,因为所在数据库不同 IF EXISTS (Select * FROM sysobjects Where object_id = OBJECT_ID(N’[dbo].[#tempTable]‘) AND type in (N’U')) Begin Drop TABLE [dbo].[tempTable] End –错误的临时表删除操作,因为临时表名已变 if exists (select * from tempdb.dbo.sysobjects where id = object_id(N’[#temptable]‘)) Begin drop table #temptable End 正确的语句如下:–正确的临时表删除操作 if object_id(’tempdb..#tempTable’) is not null Begin drop table #tempTable End 大家可以实地测试一下,看看tempDB中临时表的变化。
临时表与一般的表不同。它是保存到tempDb表中,临时表的表名与你所建的表名也不一样,因为他要为不同人的相同操作创建不同的临时表。表名存储时的格式:dbo.#tempTable_________________________________________________________00000000000Ddbo.#tempTable_________________________________________________________00000000000Edbo.#tempTable_________________________________________________________00000000000Fdbo.#tempTable_________________________________________________________000000000010通过上面你会看出它的命名规则,这个规则是16进制的。因为临时表的存储位置不同,名称是变化的,所以它的删除与一般表的删除语句是不一样的,下面的删除操作都是错误的:–错误的临时表删除操作,因为所在数据库不同 IF EXISTS (Select * FROM sysobjects Where object_id = OBJECT_ID(N’[dbo].[#tempTable]‘) AND type in (N’U')) Begin Drop TABLE [dbo].[tempTable] End –错误的临时表删除操作,因为临时表名已变 if exists (select * from tempdb.dbo.sysobjects where id = object_id(N’[#temptable]‘)) Begin drop table #temptable End 正确的语句如下:–正确的临时表删除操作 if object_id(’tempdb..#tempTable’) is not null Begin drop table #tempTable End 大家可以实地测试一下,看看tempDB中临时表的变化。另外注意:临时表是带#的,不带#不是临时表,是物理表.
一个是临时表,一个是表变量。 我们经常使用临时表和表变量,那现在我们就对临时表和表变量进行一下讨论.临时表 局部临时表 全局临时表 表变量 临时表 临时表存储在TempDB数据库中,所有的使用此SQL Server 实例的用户都共享这个TempDB,因为我们应该确保用来存储TempDB数据库的硬盘有足够的空间,以使之能够自己的增长.最好能够存储在一个拥有独立硬盘控制器上.因为这样不存在和其它的硬盘I/O进行争用. 我们很多程序员认为临时表非常危险,因为临时表有可能被多个连接所共享.其实在SQL Server中存在两种临时表:局部临时表和全局临时表,局部临时表(Local temp table)以#前缀来标识,并且只能被创建它的连接所使用.全局临时表(Global temp table)以##前缀来进行标识,并且可以和其它连接所共享. 局部临时表 局部临时表不能够被其它连接所共享的原因其实是在SQL Server 2000中自动为局部临时表的表名后面加上了一个唯一字符来标识.如: CREATE TABLE [#DimCustomer_test] ( [CustomerKey] [int] , [FirstName] [nvarchar](50) ,[MiddleName] [nvarchar](50) ,[LastName] [nvarchar](50) ) 现在我们来查看一下TempDB中 sysobjects表,我们会发现我们新创建的临时表#DimCustomer_test已经被加上了后缀: USE TempDB GO SELECT name FROM sysobjects WHERE name LIKE ’%DimCustomer%’ the Result is: name #DimCustomer_test___________________________________________________________________________________________________000000000005 全局临时表 下面我们来看一下全局临时表: CREATE TABLE [##DimCustomer_test] ( [CustomerKey] [int] , [FirstName] [nvarchar](50) ,[MiddleName] [nvarchar](50) ,[LastName] [nvarchar](50) ) 现在我们来查看一下TempDB中 sysobjects表,我们会发现我们新创建的临时表##DimCustomer_test没有被加上了后缀: USE TempDB GO SELECT name FROM sysobjects WHERE name LIKE ’%DimCustomer%’ The Result are: #DimCustomer_test___________________________________________________________________________________________________000000000005 ##DimCustomer_test --Drop test temp tables DROP TABLE [##DimCustomer_test] DROP TABLE [#DimCustomer_test] 可以看到我们刚才创建的全局临时表名字并没有被加上标识. 表变量 表变量和临时表针对我们使用人员来说并没有什么不同,但是在存储方面来说,他们是不同的,表变量存储在内存中.所以在性能上和临时表相比会更好些! 另一个不同的地方是在表连接中使用表变量时,要为此表变量指定别名.如: USE AdventureWorksDW GO DECLARE @DimCustomer_test TABLE ( [CustomerKey] [int] , [FirstName] [nvarchar](50) ,[MiddleName] [nvarchar](50) ,[LastName] [nvarchar](50) ) ---insert data to @DimCustomer_test INSERT @DimCustomer_test ( [CustomerKey] , [FirstName] ,[MiddleName] ,[LastName] ) SELECT [CustomerKey] , [FirstName] ,[MiddleName] ,[LastName] FROM DimCustomer SELECT [@DimCustomer_test].CustomerKey,SUM(FactInternetSales.OrderQuantity) FROM @DimCustomer_test INNER JOIN FactInternetSales ON @DimCustomer_test.CustomerKey = FactInternetSales.CustomerKey Group BY CustomerKey Result: Server: Msg 137, Level 15, State 2, Line 32 Must declare the variable ’@DimCustomer_test’. 如果我们对上面的查询进行更改,对查询使用别名(并且找开IO): -----in the follow script,we used the table alias. DECLARE @DimCustomer_test TABLE ( [CustomerKey] [int] , [FirstName] [nvarchar](50) ,[MiddleName] [nvarchar](50) ,[LastName] [nvarchar](50) ) INSERT @DimCustomer_test ( [CustomerKey] , [FirstName] ,[MiddleName] ,[LastName] ) SELECT [CustomerKey] , [FirstName] ,[MiddleName] ,[LastName] FROM DimCustomer SELECT t.CustomerKey,f.OrderQuantity FROM @DimCustomer_test t INNER JOIN FactInternetSales f ON t.CustomerKey = f.CustomerKey where t.CustomerKey=13513 表变量在批处理结束时自动被系统删除,所以你不必要像使用临时表表一样显示的对它进行删除.
我是#TEMP表是用在这种情况下的exec('select t.* into #temp ....报错,如果是TEMP就行
exec('select t.* into ##temp .... select * from ##temp 这样试试
if object_id('tempdb..#tmp') Is Not Null
Drop Table #tmp2.要是存储过程A中,含有调用存储过程B,而且存储过程A,B都有创建一个相同的临时表如#tmp,那么在运行时一定报错的。因此要避免这样相同的临时表名称。3.还有编译过程报错的一种情况:
create proc up_test
(
@typeid int
)
As
if object_id('tempdb..#tmp') Is Not Null
Drop Table #tmpIf @typeid=1
Begin
Select * Into #tmp From test1
End
Else
If @typeid=1
Begin
Select * Into #tmp From test2
End
Go
当点执行的时候,会报错:“数据库中已存在名为 '#tmp' 的对象。”,这样也要考虑使用不同的临时表名可以解决。
IF EXISTS (Select * FROM sysobjects Where object_id = OBJECT_ID(N’[dbo].[#tempTable]‘) AND type in (N’U'))
Begin
Drop TABLE [dbo].[tempTable]
End
–错误的临时表删除操作,因为临时表名已变
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N’[#temptable]‘))
Begin
drop table #temptable
End
正确的语句如下:–正确的临时表删除操作
if object_id(’tempdb..#tempTable’) is not null Begin
drop table #tempTable
End
大家可以实地测试一下,看看tempDB中临时表的变化。
IF EXISTS (Select * FROM sysobjects Where object_id = OBJECT_ID(N’[dbo].[#tempTable]‘) AND type in (N’U'))
Begin
Drop TABLE [dbo].[tempTable]
End
–错误的临时表删除操作,因为临时表名已变
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N’[#temptable]‘))
Begin
drop table #temptable
End
正确的语句如下:–正确的临时表删除操作
if object_id(’tempdb..#tempTable’) is not null Begin
drop table #tempTable
End
大家可以实地测试一下,看看tempDB中临时表的变化。另外注意:临时表是带#的,不带#不是临时表,是物理表.
我们经常使用临时表和表变量,那现在我们就对临时表和表变量进行一下讨论.临时表 局部临时表 全局临时表 表变量 临时表 临时表存储在TempDB数据库中,所有的使用此SQL Server 实例的用户都共享这个TempDB,因为我们应该确保用来存储TempDB数据库的硬盘有足够的空间,以使之能够自己的增长.最好能够存储在一个拥有独立硬盘控制器上.因为这样不存在和其它的硬盘I/O进行争用. 我们很多程序员认为临时表非常危险,因为临时表有可能被多个连接所共享.其实在SQL Server中存在两种临时表:局部临时表和全局临时表,局部临时表(Local temp table)以#前缀来标识,并且只能被创建它的连接所使用.全局临时表(Global temp table)以##前缀来进行标识,并且可以和其它连接所共享. 局部临时表 局部临时表不能够被其它连接所共享的原因其实是在SQL Server 2000中自动为局部临时表的表名后面加上了一个唯一字符来标识.如: CREATE TABLE [#DimCustomer_test] ( [CustomerKey] [int] , [FirstName] [nvarchar](50) ,[MiddleName] [nvarchar](50) ,[LastName] [nvarchar](50) ) 现在我们来查看一下TempDB中 sysobjects表,我们会发现我们新创建的临时表#DimCustomer_test已经被加上了后缀: USE TempDB GO SELECT name FROM sysobjects WHERE name LIKE ’%DimCustomer%’ the Result is: name #DimCustomer_test___________________________________________________________________________________________________000000000005 全局临时表 下面我们来看一下全局临时表: CREATE TABLE [##DimCustomer_test] ( [CustomerKey] [int] , [FirstName] [nvarchar](50) ,[MiddleName] [nvarchar](50) ,[LastName] [nvarchar](50) ) 现在我们来查看一下TempDB中 sysobjects表,我们会发现我们新创建的临时表##DimCustomer_test没有被加上了后缀: USE TempDB GO SELECT name FROM sysobjects WHERE name LIKE ’%DimCustomer%’ The Result are: #DimCustomer_test___________________________________________________________________________________________________000000000005 ##DimCustomer_test --Drop test temp tables DROP TABLE [##DimCustomer_test] DROP TABLE [#DimCustomer_test] 可以看到我们刚才创建的全局临时表名字并没有被加上标识. 表变量 表变量和临时表针对我们使用人员来说并没有什么不同,但是在存储方面来说,他们是不同的,表变量存储在内存中.所以在性能上和临时表相比会更好些! 另一个不同的地方是在表连接中使用表变量时,要为此表变量指定别名.如: USE AdventureWorksDW GO DECLARE @DimCustomer_test TABLE ( [CustomerKey] [int] , [FirstName] [nvarchar](50) ,[MiddleName] [nvarchar](50) ,[LastName] [nvarchar](50) ) ---insert data to @DimCustomer_test INSERT @DimCustomer_test ( [CustomerKey] , [FirstName] ,[MiddleName] ,[LastName] ) SELECT [CustomerKey] , [FirstName] ,[MiddleName] ,[LastName] FROM DimCustomer SELECT [@DimCustomer_test].CustomerKey,SUM(FactInternetSales.OrderQuantity) FROM @DimCustomer_test INNER JOIN FactInternetSales ON @DimCustomer_test.CustomerKey = FactInternetSales.CustomerKey Group BY CustomerKey Result: Server: Msg 137, Level 15, State 2, Line 32 Must declare the variable ’@DimCustomer_test’. 如果我们对上面的查询进行更改,对查询使用别名(并且找开IO): -----in the follow script,we used the table alias. DECLARE @DimCustomer_test TABLE ( [CustomerKey] [int] , [FirstName] [nvarchar](50) ,[MiddleName] [nvarchar](50) ,[LastName] [nvarchar](50) ) INSERT @DimCustomer_test ( [CustomerKey] , [FirstName] ,[MiddleName] ,[LastName] ) SELECT [CustomerKey] , [FirstName] ,[MiddleName] ,[LastName] FROM DimCustomer SELECT t.CustomerKey,f.OrderQuantity FROM @DimCustomer_test t INNER JOIN FactInternetSales f ON t.CustomerKey = f.CustomerKey where t.CustomerKey=13513 表变量在批处理结束时自动被系统删除,所以你不必要像使用临时表表一样显示的对它进行删除.
exec('select t.* into ##temp .... select * from ##temp
这样试试