February 23, 2011 at 6:40 am
Hi everyone,
Is there any way to keep one global temp table, and avoid to be changed by other SQL Script?
To be honest, I only want to create a temp table dynamically, and then I can call this temp table in other sql script. In oder to implement it, I have to create global temp table(Like the code below), and then it can be called in other place, we can also delete it if we want to do. But, in this case, we have another code list want to create same global temp table with same name, and then the data in the global temo table might be conflicted at this time. I hope to avoid this situation, do you have any solution for it? Really appricated if you can share anything with me. Thanks a lot:)
Thanks
Lindsay
DECLARE @sql VARCHAR(max)
DECLARE @sqlrun VARCHAR(max)
SET @sql = 'SELECT [AccountKey]
,[ParentAccountKey]
,[AccountCodeAlternateKey]
,[ParentAccountCodeAlternateKey]
,[AccountDescription]
,[AccountType]
,[Operator]
,[CustomMembers]
,[ValueType]
,[CustomMemberOptions]
from AdventureWorksDW.dbo.DimAccount'
SET @sqlrun = 'SELECT * INTO ##tbl from ('+@sql+') as tabletemp'
EXEC (@sqlrun)
IF object_id('tempdb..##tbl') IS not NULL
DROP table ##tbl
February 23, 2011 at 6:58 am
How I typically deal with this problem is to generate a temp table name based upon something unique to the process. In a typical case you can use the PID (processID) as a part of the table name. This assumes the table name or just the PID can be handed off to other processes that need to access the table.
That may not appropriate if you want to avoid dynamic SQL or have no easy way to hand off a PID to other processes. In that case you can use single temp table with process or context ID column. That way two or more different processes can access the temp table simultaneously as long as they know their "context ID". In this case you may decide to use a permanent "temp" table unless you need it to be in tempdb for performance (or other) reasons.
The probability of survival is inversely proportional to the angle of arrival.
February 23, 2011 at 9:38 pm
Thanks a lot sturner ^_^
I modified the gloable temp table name with PID info, in case there is any conflict with other run. but I found I can't deliver the table name as a parameter in the sp_executesql, do you have any suggestion? Thanks a lot^_^
Thanks
Ling
DECLARE @sqlVARCHAR(max) SET @sql = NULL
DECLARE @tblResultNamevarchar(50) SET @tblResultName = '##tblResult_'+ CONVERT(varchar(4),@@SPID)
DECLARE @sqlInsertVARCHAR(max) SET @sqlInsert = NULL
DECLARE @ResultCountInt SET @ResultCount = 1
select @@SPID
select @tblResultName
Set @sql = 'select [AccountKey]
,[ParentAccountKey]
,[AccountCodeAlternateKey]
,[ParentAccountCodeAlternateKey]
,[AccountDescription]
,[AccountType]
,[Operator]
,[CustomMembers]
,[ValueType]
,[CustomMemberOptions]
from ADW1.dbo.DimAccount
'
IF Object_Id('tempdb..'+@tblResultName) IS NOT NULL EXEC('drop table ' + @tblResultName)
SET @sqlInsert = 'SELECT * INTO ' + @tblResultName + ' FROM (' + @sql + ') AS TempResultTable'
EXEC(@sqlInsert)
EXEC sp_executesql
N'SELECT @ResultCountOut= COUNT(*) FROM @tbl',
N'@tbl varchar(10), @ResultCountOut INT OUT',
@tbl = @tblResultName,
@ResultCountOut = @ResultCount OUT
IF Object_Id('tempdb..'+@tblResultName) IS NOT NULL EXEC('drop table ' + @tblResultName)
February 23, 2011 at 10:13 pm
February 23, 2011 at 11:35 pm
Thanks a lot SQLkiwi ^_^
Thanks
Ling
February 24, 2011 at 7:05 am
In you specific case, I think this methodology would work:
set @sqlInsert = 'SELECT @ResultCountOut= COUNT(*) FROM ' + @tblResultName
EXEC sp_executesql
@sqlInsert,
@ResultCountOut INT OUT',
@ResultCountOut = @ResultCount OUT
The probability of survival is inversely proportional to the angle of arrival.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply