Создайте временную таблицу с помощью динамического SQL
Я хотел создать много временных таблиц в цикле и вставить данные, но когда я запустил тест с этим простым динамическим SQL, он прошел успешно, но таблицы не были созданы. Может ли кто-нибудь помочь мне? Заранее спасибо.
IF OBJECT_ID('tempdb..#blankOuputLevel2') IS NOT NULL
BEGIN
DROP TABLE #blankOuputLevel2
END
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'CREATE TABLE #blankOuputLevel2 (tjob NVARCHAR(30));'
EXEC sp_executesql @sql
Этот код работает успешно.
Затем я запускаю SELECT, показанный ниже, и получаю сообщение об ошибке:
Недопустимое имя объекта '#blankOuputLevel2'
SELECT * FROM #blankOuputLevel2
в динамическом SQL временная таблица, созданная в динамическом SQL, удаляется автоматически (существует только в рамках запроса). вы можете использовать реальную таблицу или глобальную временную таблицу (##TableName), которая может видеть вне области динамический запрос (эта сессия и другая сессия).
Ответы 1
Вот как работают (локальные) временные таблицы #Temp.
Они автоматически удаляются, когда выходят за рамки.
Ваша временная таблица создается в контексте/области действия хранимой процедуры sp_executesql и автоматически удаляется при возврате этой хранимой процедуры.
Вы можете попробовать использовать глобальные временные таблицы ##Temp, но, как указал Аарон Бертран: «Глобальные временные таблицы полезны только тогда, когда вы можете гарантировать одновременный параллелизм; если два пользователя запускают один и тот же код и ссылаются ##whatever, они будут топтаться повсюду. друг друга. И когда вы можете гарантировать одновременный параллелизм, я бы просто использовал постоянную таблицу».
Во многих случаях вы можете поместить весь свой код в динамическую строку SQL для выполнения одним пакетом с помощью одного вызова sp_executesql:
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'CREATE TABLE #blankOuputLevel2 (tjob NVARCHAR(30)); '
SET @sql = @sql + 'INSERT INTO #blankOuputLevel2 .....;'
SET @sql = @sql + 'SELECT ... FROM #blankOuputLevel2 .....;'
SET @sql = @sql + '... the rest of your logic using #blankOuputLevel2 ...'
EXEC sp_executesql @sql
В этом случае локальная временная таблица #blankOuputLevel2 будет видна коду, который вы поместили в @sql, и она будет удалена после завершения sp_executesql.
Вы можете запускать несколько экземпляров этого динамического кода одновременно, и каждый экземпляр получит свою собственную отдельную временную таблицу.
Дополнительные сведения о различных временных таблицах в SQL Server см. в этом вопросе Локальные и глобальные временные таблицы в SQL Server:
Табличные переменные (
DECLARE @t TABLE) видны только создавшему их соединению и удаляются при завершении пакета или хранимой процедуры.Локальные временные таблицы (
CREATE TABLE #t) видны только соединение, которое его создает, и удаляются, когда соединение закрыто.Глобальные временные таблицы (
CREATE TABLE ##t) видны всем, и удаляются, когда закрываются все соединения, которые ссылались на них.Постоянные таблицы Tempdb (
USE tempdb CREATE TABLE t) видны всем и удаляются при перезапуске сервера.
Временные таблицы автоматически удаляются, когда они выходят за пределы области видимости. если явно не удалено с помощью DROP TABLE:
- Локальная временная таблица, созданная в хранимой процедуре, автоматически удаляется по завершении хранимой процедуры. Таблица может быть на которые ссылаются любые вложенные хранимые процедуры, выполняемые хранимым
Процедура, которая создала таблицу. На таблицу нельзя ссылаться
процесс, вызвавший хранимую процедуру, создавшую таблицу.- Все остальные локальные временные таблицы удаляются автоматически в конце текущего сеанса.
- Глобальные временные таблицы автоматически удаляются, когда завершается сеанс, создавший таблицу, и все остальные задачи завершаются. перестал на них ссылаться. Связь между задачей и таблица поддерживается только на время жизни одного Transact-SQL
заявление. Это означает, что глобальная временная таблица удалена завершение последней активной инструкции Transact-SQL
ссылка на таблицу после завершения сеанса создания.
Глобальные временные таблицы полезны только тогда, когда вы можете гарантировать одиночный параллелизм; если два пользователя запустят один и тот же код и ссылаются на ##что угодно, они затопчут друг друга. И когда вы можете гарантировать одновременный параллелизм, я бы просто использовал постоянную таблицу.
Верный момент, @AaronBertrand
Спасибо @VladimirBaranov и AaronBertrand за очень полезную информацию!
Вам нужно создать и использовать таблицу в динамическом SQL.