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
Re: Replace regular table with temporary in SQL Server
Quote:
Originally Posted by
chapran
I get an error Invalid object name '#TempTable'
Please help me with this change.
Where and how do you CREATE this #TempTable?
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
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.
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
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
Quote:
If more than one temporary table is created inside a single stored procedure or batch, they must have different names.
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
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
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.
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!
Re: Replace regular table with temporary in SQL Server
I cannot do this because I have dynamic selection (see my initial code).
Thank you
Re: Replace regular table with temporary in SQL Server
Quote:
Originally Posted by
chapran
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.
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
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).
Re: Replace regular table with temporary in SQL Server
Thank you very much for your help