Geek asks and answers

Access SQL Server temporary tables created in different areas

I am writing a stored procedure for SQL Server 2008 in which I need to extract information from a set of tables. I do not know the structure of these tables in advance. There is another table in the same database that tells me the names and types of fields in this table.

I'm doing it:

declare @sql nvarchar(max)

set @sql = 'select ... into #new_temporary_table ...'
exec sp_executesql @sql

Then I iterate doing:

set @sql = 'insert into #another_temporary_table ... select ... from #new_temporary_table'
exec sp_executesql @sql

After that I delete the temporary table. This happens in a loop, so the table is created, populated and discarded many times, each time with different columns.

Error with error:

Invalid object name: #new_temporary_table.

After some searches, I found that:

, , select into #new_temporary_table.. insert into ... . , .

@sql , , .

? .

+3
3

, script.

, "" > "" > " " > "SQL Server" > " " - " ..." 256 (8192).

8192, , . . PRINT @sql; ( ):

SELECT sql FROM (SELECT @sql) AS x(sql) FOR XML PATH;

. , XML, , , . > >, XML-, , . , . FWIW , XML, :

http://connect.microsoft.com/SQLServer/feedback/details/425990/ssms-allow-same-semantics-for-xml-docs-as-query-windows

+1

global temp, (, newid()) temp.

declare @sql varchar(2000)
declare @contextid varchar(50) = convert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 1, 4)))
set @sql = 'select getdate() as stuff into ##new_temporary_table_' + @contextid
exec (@sql)
+3

# temp tables (not global) are available in the area they created below. So you could do something like ...

while (your_condition = 1) begin
    set @sql = 'select ... into #temp1 ...from blah
        exec sp_do_the_inserts'
    exec(@sql)
end

sp_do_the_inserts might look like ...

select * into #temp2 from #temp1
....your special logic here....

This assumes that you create sp_do_the_inserts in advance, of course. I don't know if this serves you.

+1
source

More articles:


All Articles