SQL Server - 如果不存在则创建临时表 [英] SQL Server - Create temp table if doesn't exist

问题描述

在我的 SQL Server 2012 环境中，我创建了一系列存储过程，它们在它们之间传递预先存在的临时表(我在这里尝试了不同的架构，但由于要求/程序).

In my SQL Server 2012 environment, I've created a series of stored procedures that pass pre-existing temporary tables among themselves (I have tried different architectures here, but wasn't able to bypass this due to the nature of the requirements / procedures).

我想做的是在存储过程中检查是否已经创建了临时表，如果没有，则创建它.

What I'm trying to do is to, within a stored procedure check if a temporary table has already been created and, if not, to create it.

我当前的 SQL 如下所示:

My current SQL looks as follows:

IF OBJECT_ID('tempdb..#MyTable') IS NULL
    CREATE TABLE #MyTable
    (
        Col1 INT,
        Col2 VARCHAR(10)
        ...
    );

但是当我尝试运行它当表已经存在时，我收到错误消息

But when I try and run it when the table already exists, I get the error message

数据库中已经有一个名为#MyTable"的对象

There is already an object named '#MyTable' in the database

所以它似乎并没有简单地忽略 If 语句中的那些行.

So it seems it doesn't simply ignore those lines within the If statement.

有没有办法做到这一点 - 如果临时表尚不存在，则创建一个临时表，否则，使用内存中已有的表?

Is there a way to accomplish this - create a temp table if it doesn't already exist, otherwise, use the one already in memory?

谢谢！

更新:

无论出于何种原因，根据@RaduGheorghiu 从评论中提出的建议，我发现系统创建了一个临时表，其名称类似于 dbo.#MyTable________________________________00000000001B1

For whatever reason, following @RaduGheorghiu's suggestion from the comments, I found out that the system creates a temporary table with a name along the lines of dbo.#MyTable________________________________________________0000000001B1

这就是我找不到的原因吗?有什么办法可以改变吗?这对我来说是新的....

Is that why I can't find it? Is there any way to change that? This is new to me....

推荐答案

点击此处的链接，http://weblogs.sqlteam.com/mladenp/archive/2008/08/21/SQL-Server-2005-temporary-tables-bug-feature-or-expected-behavior.aspx

好像需要使用 GO 语句.

It seems as though you need to use the GO statement.

这篇关于SQL Server - 如果不存在则创建临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！