Cannot access table created from query string

Transact-SQL
1

The code below will create some test data and then build a query string of a pivot that puts the pivot output into another table. The resulting table will be the pivoted data.

If I 'select * #tbl_test_2' on the pivoted data AFTER the EXECUTE(@query) I get 'Invalid object name '#tbl_test_2'. I build the select statement INTO the built query string it works.

Why can't I access the table after it was created with EXECUTE(@query) ?

Greg

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

--CREATE TABLE [dbo].[#tbl_test](
--[status] [int] NOT NULL,
--[user_id] [int] NOT NULL,
--[value] [int] NOT NULL
--) ON [PRIMARY]

--INSERT INTO [dbo].#tbl_test VALUES (1,1,11)
--INSERT INTO [dbo].#tbl_test VALUES (2,1,1)
--INSERT INTO [dbo].#tbl_test VALUES (10,1,89)
--INSERT INTO [dbo].#tbl_test VALUES (4,1,56)
--INSERT INTO [dbo].#tbl_test VALUES (3,2,156)
--INSERT INTO [dbo].#tbl_test VALUES (12,2,75)

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

declare @query varchar(1000)
set @query = 'SELECT *
INTO #tbl_test_2
FROM
(
SELECT [status]
,[user_id]
,[value]
FROM [dbo].[#tbl_test]

)AS source
PIVOT
(
MAX([value])
FOR [status] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) as pvt
select * from #tbl_test_2'

execute(@query)

The above sql works. If I call select * from #tbl_test_2 after execute(@query), I get invalid object.

2

the executed query happens in another context so the temp table is created then destroyed. you could try this:

declare @sql nvarchar(4000) = 'select 1 as foo into ##bar'
exec sp_executesql @sql
select * from ##bar

but know that ## means the temp table is global

1 Like
3

That got it. Thank you.

Greg