dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Replace regular table with temporary in SQL Server

  1. #1
    Join Date
    Jan 2013
    Posts
    83

    Replace regular table with temporary in SQL Server

    Replace regular table with temporary in SQL Server
    I have a stored procedure which has this code at the beginning (it is simplified to make it easier to understand)

    Code:
    ALTER PROCEDURE ____TempTableTest 
    @CompanyID int = NULL,
    @MinDate smalldatetime,
    @MaxDate smalldatetime
    AS
    BEGIN
    
    SET NOCOUNT ON
    DECLARE @sql nvarchar(1000)
    DECLARE @QtyOfDays int
    SELECT @QtyOfDays = DateDiff(day, @MinDate, @MaxDate)
    
    IF @QtyOfDays<1 
    	SET @QtyOfDays = 1
    ELSE
    	SET @QtyOfDays = @QtyOfDays
    
    	PRINT @QtyOfDays
    	IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL DROP TABLE #TempTable
    
    ----Create Num table just for populating #Dates with all dates
    SET @sql = N'SELECT TOP ' + CAST(@QtyOfDays AS varchar(5)) + N' n = IDENTITY(INT, 1, 1) INTO Num FROM syscomments a CROSS JOIN syscomments b'
    
    EXEC sp_ExecuteSQL @sql
    
    SELECT * FROM Num
    
    
    
    GO
    By some reason I want to replace the regular table Num with the temporary table #TempTable
    If I make changes to this:

    Code:
    ALTER PROCEDURE ____TempTableTest 
    @CompanyID int = NULL,
    @MinDate smalldatetime,
    @MaxDate smalldatetime
    AS
    BEGIN
    
    SET NOCOUNT ON
    DECLARE @sql nvarchar(1000)
    DECLARE @QtyOfDays int
    SELECT @QtyOfDays = DateDiff(day, @MinDate, @MaxDate)
    
    IF @QtyOfDays<1 
    	SET @QtyOfDays = 1
    ELSE
    	SET @QtyOfDays = @QtyOfDays
    
    	PRINT @QtyOfDays
    	IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL DROP TABLE #TempTable
    
    ----Create Num table just for populating #Dates with all dates
    SET @sql = N'SELECT TOP ' + CAST(@QtyOfDays AS varchar(5)) + N' n = IDENTITY(INT, 1, 1) INTO #TempTable FROM syscomments a CROSS JOIN syscomments b'
    
    EXEC sp_ExecuteSQL @sql
    
    SELECT * FROM #TemptTable
    
    GO
    I get an error Invalid object name '#TempTable'
    Please help me with this change.

    Thank you

  2. #2
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    19,379

    Re: Replace regular table with temporary in SQL Server

    Quote Originally Posted by chapran View Post
    I get an error Invalid object name '#TempTable'
    Please help me with this change.
    Where and how do you CREATE this #TempTable?
    Victor Nijegorodov

  3. #3
    Join Date
    Jan 2013
    Posts
    83

    Re: Replace regular table with temporary in SQL Server

    Right here by using SELECT INTO:
    Code:
    ----Create Num table just for populating #Dates with all dates
    SET @sql = N'SELECT TOP ' + CAST(@QtyOfDays AS varchar(5)) + N' n = IDENTITY(INT, 1, 1) INTO #TempTable FROM syscomments a CROSS JOIN syscomments b'
    
    EXEC sp_ExecuteSQL @sql

  4. #4
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    12,986

    Re: Replace regular table with temporary in SQL Server

    SELECT INTO doesn't create a table - it only inserts records into an existing table. This worked before because the table already existed.

    Btw, if you use temporary table (named with a # or ## prefix, like #mytmptable or ##myglobaltmptable) you need to remember to delete the temp table when you are finished using it.

    For this reason consider using a table variable (named with a @ prefix like @mytable). One advantahe of using a table variable is it doesn't need to be deleted.
    Last edited by Arjay; June 11th, 2019 at 04:51 PM.

  5. #5
    Join Date
    Jan 2013
    Posts
    83

    Re: Replace regular table with temporary in SQL Server

    Sorry, but the sources I was working with say that the table may be created and populated with SELECT INTO, like my working code does:
    Code:
    SET @sql = N'SELECT TOP ' + CAST(@QtyOfDays AS varchar(5)) + N' n = IDENTITY(INT, 1, 1) INTO Num FROM syscomments a CROSS JOIN syscomments b'
    
    EXEC sp_ExecuteSQL @sql
    
    SELECT * FROM Num
    And maybe I used the bad way to fix the problem with replacing standard table Num with the temporary, but it works when I use global temporary table. Like this:
    Code:
    	SET @sql = N'SELECT TOP ' + CAST(@QtyOfDays AS varchar(5)) + N' n = IDENTITY(INT, 1, 1) INTO ##TempTable FROM syscomments a	CROSS JOIN syscomments b'
    
    	EXEC sp_ExecuteSQL @sql
    This code DOES CREATE and populate the temporary table.

    Thank you

  6. #6
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    19,379

    Re: Replace regular table with temporary in SQL Server

    Do you use this #TempTable in your stored procedure only once (with shown SELECT INTO) or somewhere else?
    Note that according with MSDN
    If more than one temporary table is created inside a single stored procedure or batch, they must have different names.
    Victor Nijegorodov

  7. #7
    Join Date
    Jan 2013
    Posts
    83

    Re: Replace regular table with temporary in SQL Server

    Any table I tried - Num, #TempTable and ##TempTable are used once. Num and ##TempTable work fine, #TempTable doesn't.
    Thank you

  8. #8
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    19,379

    Re: Replace regular table with temporary in SQL Server

    Well, I've just made a simple test with SSMS v17.9.1 and SQL Server 13.0.4224.16
    I use this procedure:
    Code:
    CREATE PROCEDURE USP_TestTempTable 
    	-- Add the parameters for the stored procedure here
    	@someID int = 0
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
    	SELECT @someID AS ID, * INTO #textemp FROM [Status] where [Status].StatusID ='U'
    	SELECT * FROM #textemp
    END
    GO
    And it works! The result is:
    Code:
    11	U	unvollzogen	2014-12-16 08:54:00.000	1	2014-12-16 08:54:00.000	1	1
    Victor Nijegorodov

  9. #9
    Join Date
    Jan 2013
    Posts
    83

    Re: Replace regular table with temporary in SQL Server

    I get this:
    Code:
     
    Msg 208, Level 16, State 1, Procedure dbo.USP_TestTempTable, Line 11 [Batch Start Line 2]
    Invalid object name 'Status'.
    I understand that I have to have the table Status. It is going to be different story. What about adjusting my existing code I initially posted:
    Code:
    ALTER PROCEDURE ____TempTableTest 
    @CompanyID int = NULL,
    @MinDate smalldatetime,
    @MaxDate smalldatetime
    AS
    BEGIN
    
    SET NOCOUNT ON
    DECLARE @sql nvarchar(1000)
    DECLARE @QtyOfDays int
    SELECT @QtyOfDays = DateDiff(day, @MinDate, @MaxDate)
    
    IF @QtyOfDays<1 
    	SET @QtyOfDays = 1
    ELSE
    	SET @QtyOfDays = @QtyOfDays
    
    	PRINT @QtyOfDays
    	IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL DROP TABLE #TempTable
    
    ----Create Num table just for populating #Dates with all dates
    SET @sql = N'SELECT TOP ' + CAST(@QtyOfDays AS varchar(5)) + N' n = IDENTITY(INT, 1, 1) INTO Num FROM syscomments a CROSS JOIN syscomments b'
    
    EXEC sp_ExecuteSQL @sql
    
    SELECT * FROM Num
    I wanted to replace Num with #TempTable. My SP did not require preexisting any table.
    Actually I found the solution with using global temporary table ##TempTable. I'm just not sure if this solution will not bring some other problems. I never used global temporary tables before. I'm using
    Code:
    	IF OBJECT_ID('tempdb..##TempTable') IS NOT NULL DROP TABLE ##TempTable --Using not global #TempTable doesn't work
    at the beginning and at the end of the stored procedure to minimize the risk that my ##TempTable will be visible to other users.

    Thank you


    Thank you.

  10. #10
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    19,379

    Re: Replace regular table with temporary in SQL Server

    The problem seems to be in the
    If you just add this text:
    Code:
    SELECT TOP 31 n = IDENTITY(INT, 1, 1) INTO #TempTable FROM syscomments a CROSS JOIN syscomments b
    instead of
    Code:
    EXEC sp_ExecuteSQL @sql
    it will work!
    Victor Nijegorodov

  11. #11
    Join Date
    Jan 2013
    Posts
    83

    Re: Replace regular table with temporary in SQL Server

    I cannot do this because I have dynamic selection (see my initial code).
    Thank you

  12. #12
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    19,379

    Re: Replace regular table with temporary in SQL Server

    Quote Originally Posted by chapran View Post
    I cannot do this because I have dynamic selection (see my initial code).
    Thank you
    Then use table variable instead. However, in such a case you will have to declare/define all the columns in it.
    Victor Nijegorodov

  13. #13
    Join Date
    Jan 2013
    Posts
    83

    Re: Replace regular table with temporary in SQL Server

    Can you tell me if using ##TempTable is a bad way? If it is the same as #TempTable then I'm fine and wouldn't want to fight with this stuff anymore because my main area is VB6 development. SQL Server is not.

    Thank you

  14. #14
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    19,379

    Re: Replace regular table with temporary in SQL Server

    It is not a "bad way".
    However, there may be a possibility for other users/connections access the same global table:
    https://www.itprotoday.com/sql-serve...ocal-vs-global
    To reduce the danger of such a situation you could try to create a global temp table with a GUID-like name (you can use NEWID() + CONVERT() to achieve it).
    Victor Nijegorodov

  15. #15
    Join Date
    Jan 2013
    Posts
    83

    Re: Replace regular table with temporary in SQL Server

    Thank you very much for your help

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)