Replace regular table with temporary in SQL Server

[chapran]

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)

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:

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

[VictorN]

I get an error Invalid object name '#TempTable'
Please help me with this change.

Where and how do you CREATE this #TempTable?

[chapran]

Right here by using SELECT INTO:

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

[Arjay]

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.

[chapran]

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:

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:

	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

[VictorN]

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.

[chapran]

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

[VictorN]

Well, I've just made a simple test with SSMS v17.9.1 and SQL Server 13.0.4224.16
I use this procedure:

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:

11	U	unvollzogen	2014-12-16 08:54:00.000	1	2014-12-16 08:54:00.000	1	1

[chapran]

I get this:

 
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:

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

	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.

[VictorN]

The problem seems to be in the
If you just add this text:

SELECT TOP 31 n = IDENTITY(INT, 1, 1) INTO #TempTable FROM syscomments a CROSS JOIN syscomments b

instead of

EXEC sp_ExecuteSQL @sql

it will work!

[chapran]

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

[VictorN]

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.

[chapran]

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

[VictorN]

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

[chapran]

Thank you very much for your help