Is there any way to keep one global temp table, and avoid to be changed by other SQL Script?

  • 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

  • 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.

  • 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)

  • Thanks a lot SQLkiwi ^_^

    Thanks

    Ling

  • 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