删除 SQL Server 中的临时表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9784599/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
removing a temp table in SQL server?
提问by SOLDIER-OF-FORTUNE
I am having problems removing a temp table in SQL server.
我在删除 SQL Server 中的临时表时遇到问题。
I have a stored procedure but when I run in via my application it says:
我有一个存储过程，但是当我通过我的应用程序运行时，它说：
"There is already an object named '#WeekList' in the database"
“数据库中已经有一个名为‘#WeekList’的对象”
when i try to drop the table i get the following message:
当我尝试放下桌子时，我收到以下消息：
Cannot drop the table '#WeekList', because it does not exist or you do not have permission.
无法删除表“#WeekList”，因为它不存在或您没有权限。
My SP is as follows:
我的SP如下：
USE [test_staff]
GO
/****** Object: StoredProcedure [dbo].[sp_create_week_list] Script Date: 03/20/2012 09:35:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_create_week_list]
AS
CREATE TABLE #WeekList
(
month_date date
)
DECLARE @REPORT_DATE DATETIME, @WEEK_BEGINING VARCHAR(10)
SELECT @REPORT_DATE = '2011-01-19T00:00:00'
--SELECT @REPORT_DATE = GETDATE() -- should grab the date now.
SELECT @WEEK_BEGINING = 'MONDAY'
IF @WEEK_BEGINING = 'MONDAY'
SET DATEFIRST 1
ELSE IF @WEEK_BEGINING = 'TUESDAY'
SET DATEFIRST 2
ELSE IF @WEEK_BEGINING = 'WEDNESDAY'
SET DATEFIRST 3
ELSE IF @WEEK_BEGINING = 'THURSDAY'
SET DATEFIRST 4
ELSE IF @WEEK_BEGINING = 'FRIDAY'
SET DATEFIRST 5
ELSE IF @WEEK_BEGINING = 'SATURDAY'
SET DATEFIRST 6
ELSE IF @WEEK_BEGINING = 'SUNDAY'
SET DATEFIRST 7
DECLARE @WEEK_START_DATE DATETIME, @WEEK_END_DATE DATETIME
--GET THE WEEK START DATE
SELECT @WEEK_START_DATE = @REPORT_DATE - (DATEPART(DW, @REPORT_DATE) - 1)
--GET THE WEEK END DATE
SELECT @WEEK_END_DATE = @REPORT_DATE + (7 - DATEPART(DW, @REPORT_DATE))
PRINT 'Week Start: ' + CONVERT(VARCHAR, @WEEK_START_DATE)
PRINT 'Week End: ' + CONVERT(VARCHAR, @WEEK_END_DATE)
DECLARE @Interval int = datediff(WEEK,getdate(),@WEEK_START_DATE)+1
SELECT Start_Week=@WEEK_START_DATE
, End_Week=@WEEK_END_DATE
INTO #WeekList
WHILE @Interval <= 0
BEGIN
set @WEEK_START_DATE=DATEADD(WEEK,1,@WEEK_START_DATE)
set @WEEK_END_DATE=DATEADD(WEEK,1,@WEEK_END_DATE)
INSERT INTO #WeekList values (@WEEK_START_DATE,@WEEK_END_DATE)
SET @Interval += 1;
END
SELECT
CONVERT(VARCHAR(11), Start_Week, 106) AS 'Start',
CONVERT(VARCHAR(11), End_Week, 106) AS 'End',
DATEDIFF(DAY, 0, Start_Week) / 7 AS week_ref -- create the unique week reference number
FROM #WeekList
ORDER BY Start_Week DESC
DROP TABLE #WeekList
回答by Brijesh Patel
In the SP there is a problem in following statement.
在 SP 中，以下语句存在问题。
SELECT Start_Week=@WEEK_START_DATE
, End_Week=@WEEK_END_DATE
INTO #WeekList
Instead of that try this one, it may work.
而不是尝试这个，它可能会起作用。
INSERT INTO #WeekList
SELECT Start_Week=@WEEK_START_DATE
, End_Week=@WEEK_END_DATE
回答by Andomar
You can drop the #YourTabletemporary table using this code:
您可以#YourTable使用以下代码删除临时表：
if exists (select * from tempdb.sys.tables where name like '#YourTable%')
drop table #YourTable
Best place to do this is right before you run insert intoto create the table.
执行此操作的最佳位置是在运行insert into创建表之前。
回答by kaj
In the middle of the code you have:
在代码中间，您有：
select ...
into #WeekList
This will try to create a new temporary table called #WeekList which you'd already created using the CREATE TABLE statement at the top of the proc
这将尝试创建一个名为 #WeekList 的新临时表，您已经使用 proc 顶部的 CREATE TABLE 语句创建了该表
If you change the insert to the style
如果将插入更改为样式
insert into #WeekList (columns)
select ...
Then you will get rid of the error
然后你将摆脱错误
回答by Vetrivel mp
--Check if it exists
--检查是否存在
IF OBJECT_ID('tempdb..#temptable') IS NOT NULL
BEGIN
drop table #temptable
END
and while using temporary table insert, specify column names externally like
并在使用临时表插入时，在外部指定列名，如
insert into #temptable(col1,col2..) values(1,2,..)
回答by KuldipMCA
Use table variable inplace of the #Temp table it's more better option. If you want to use #temp table then check for the existance of the #temp table in the Tempdb database then you are able to get that #temp table exist or not.
使用表变量代替#Temp 表，这是更好的选择。如果您想使用#temp 表然后检查Tempdb 数据库中#temp 表的存在，那么您就可以获取该#temp 表是否存在。
回答by Matt Fellows
The only thing I can see that could hint at what's wrong there is the lack of BEGIN and END? I use this all the time, but I always put BEGIN and END around the sprocs code...
我能看到的唯一可以暗示有什么问题的东西是缺少 BEGIN 和 END？我一直在使用它，但我总是在 sprocs 代码周围放置 BEGIN 和 END ......
回答by M?oz
To check if a temp table exists:
要检查临时表是否存在：
IF OBJECT_ID(N'tempdb..#yourTempTableName') IS NOT NULL
DROP TABLE #yourTempTableName;
Note as mentioned in the other answers you have created a temp table #WeekList with only one column, you then try to re-create the temp table using the short-hand SELECT ... INTO #Weeklist; which in your case you are selecting two columns, this is where the issue is occurring. I would remove the CREATE TABLEstatement at the beginning of your code and just use the SELECT ... INTOstatement.
请注意，如其他答案中所述，您创建了一个只有一列的临时表 #WeekList，然后尝试使用简写重新创建临时表SELECT ... INTO #Weeklist；在您的情况下，您选择了两列，这就是发生问题的地方。我会删除CREATE TABLE代码开头的语句，然后只使用该SELECT ... INTO语句。
回答by Anoo S Pillai
The root cause of the problem is that a temporary table with the same name is being created two times in the same batch. First by CREATE TABLE statement and second by SELECT .. INTO statement As per Microsoft BOL, "If more than one temporary table is created inside a single stored procedure or batch, they must have different names."
问题的根本原因是同一个批处理中创建了两次同名的临时表。首先是 CREATE TABLE 语句，其次是 SELECT .. INTO 语句 根据 Microsoft BOL，“如果在单个存储过程或批处理中创建了多个临时表，它们必须具有不同的名称。”
This is a feature by design as per the clarification on the Microsoft Connect Bug ID 666430.
根据 Microsoft Connect 错误 ID 666430 上的说明，这是一项设计功能。
Please see the link temporary-table-could-not-be-re-createdfor further information
请参阅链接临时表无法重新创建以获取更多信息
