Как правильно проверить, существует ли временная таблица в SQL Server 2005?

У меня есть запрос, в который я вставляю некоторые значения из таблицы:

SELECT ID, NAME INTO #tmpTable1
FROM TableOriginal

Первое исполнение выполняется нормально, если я нажму F5 (Запуск) в MSSMS (Microsoft Sql Server Management Studio), произошла ошибка:

Msg 2714, уровень 16, состояние 6, строка 4
В базе уже есть объект с именем "# tmpTable1".

Хорошо. Я решил проверить перед вставкой данных из TableOriginal в #tmpTable1, используя:

IF OBJECT_ID('tempdb.#tmpTable1') IS NOT NULL  
  DROP TABLE #tmpTable1

Не работает, ошибка отображается снова, как указано выше.

Я видел в базе данных tempdb следующее временное имя таблицы:

dbo.#tmpTable1__________________0000007

Почему? Каждый раз, когда вы создаете временную таблицу (используя первый запрос), имя таблицы будет генерироваться автоматически в MSSMS?

Как удалить существующую временную таблицу для создания новой таблицы с новыми значениями?

Ответ 1

Вы чертовски близки - вам нужно использовать две точки в чеке:

IF OBJECT_ID('tempdb..#tmpTable1') IS NOT NULL  
                    ** 
                    |
                  use two dots here!

В основном, это говорит: проверьте tempDB, и мне все равно, какая схема находится в таблице

Как справедливо сказал Джо: это не на 100% правильно: он не проверяет каждую схему - он будет проверять только схему владельца по умолчанию - обычно dbo. Так что это тоже сработало бы:

IF OBJECT_ID('tempdb.dbo.#tmpTable1') IS NOT NULL

Если вам удалось создать свои объекты в схеме, отличной от владельца по умолчанию, тогда вам нужно явно указать схему, на которую вы ссылаетесь. Но временные таблицы в tempDB действительно создаются в схеме dbo.

Ответ 2

Это не ответ на вопрос, просто нужно временно отправить ответ на бит о том, чтобы поставить .dbo. vs. .. при ссылке на таблицу #temp.

Я не мог найти способ сделать таблицу #temp фактически принадлежащей чему-либо, кроме dbo. Попробуйте:

CREATE SCHEMA blat;
GO

CREATE TABLE blat.#pound(id INT);
GO

SELECT 
   OBJECT_ID('tempdb..#pound'), 
   OBJECT_ID('tempdb.dbo.#pound'), 
   OBJECT_ID('tempdb.blat.#pound');

USE tempdb;
GO

SELECT [object_id], SCHEMA_NAME([schema_id]) 
  FROM sys.objects 
  WHERE name LIKE '#pound%';

Результаты:

-1222354987    -1222354987    -1222354987

-1222354987    dbo

Это было на SQL Server 2012. Я протестировал это на SQL Server 2005, и единственная разница была в том, что значения object_id были положительными. Я также пробовал:

схема, фактически существующая в tempdb, называемая blat
таблица blat.#pound, созданная пользователем, чья схема по умолчанию blat
оба указанных выше

Во всех трех случаях были достигнуты те же результаты, что и выше.

Вы также не можете создать две таблицы #temp с тем же именем в разных схемах:

CREATE TABLE blat.#flab(id INT);
CREATE TABLE dbo.#flab(id INT);

Результат:

Msg 2714, уровень 16, состояние 6
В базе данных уже есть объект с именем #flab.

Это не проблема синтаксического анализа (например, многие проблемы с таблицей #temp); вы можете запускать эти два оператора отдельно и получать ту же ошибку.

Итак, это длинный способ сказать, вам не нужно указывать схему при разрешении имени таблицы #temp, она всегда будет создана в dbo и разрешении, по крайней мере, под OBJECT_ID будет игнорировать указанную схему (OBJECT_SCHEMA_NAME также всегда возвращает dbo при запуске в контексте #tempdb, но не в какой-либо другой базе данных). Все ставки отключены, если вы попытаетесь запросить schema_id в tempdb.sys.objects.

Ответ 3

Определенная таким образом таблица Temp будет существовать до тех пор, пока соединение, создавшее ее, будет открыто. Как правило, нет необходимости проверять, существует ли это или отбрасывать его вручную, потому что у вас есть полный контроль внутри вашего соединения, но если вам действительно нужно его проверить, вы можете проверить tempdb.dbo. # TempTable.

Ответ 4

CREATE TABLE #temptable (col1 int);
GO
INSERT INTO #temptable
VALUES (10);
GO
SELECT * FROM #temptable;
GO
IF OBJECT_ID(N'tempdb..#temptable', N'U') IS NOT NULL 
DROP TABLE #temptable;
GO
--Test the drop.
SELECT * FROM #temptable;

Ответ 5

IF OBJECT_ID ('tempdb.dbo. # tmpTable1%') НЕ НЕТ :)