I am trying to replicate some code from SQL, and need to create a custom table insert... my SQL query uses a temp table #ELL. When I try to deploy & execute, it does not understand the temp table. Can temp tables be used here? IE. #ELL.. I just need to have it hold some info that later in the query will populate the new custom table in TimeXtender.
Can you use temp tables (sql - #tablename) in custom table insert SQL queries? This is for a custom insert table.
Best answer by Thomas Lind
Hi Purnima
Sure you can. I made this article about splitting out currencies on individual dates. Hover I found, despite the suggested method being true that there was issues with the first date. So I had to make my script differently.
In the end I worked out that I needed to make it as an StoredProcedure, like so.

CREATE PROCEDURE [DSA].[spExchangeRates]
@iCurrencyCode int = NULL,
@iMaxCurrencyCode int = NULL
AS
BEGIN
CREATE TABLE #TempTable
(
[CurrencyCode] int,
[ExchangeRate] decimal(38,3),
[FromDate] datetime,
[ToDate] datetime
)
WHILE(@iCurrencyCode <= @iMaxCurrencyCode)
BEGIN
INSERT INTO #TempTable
(
[CurrencyCode],
[ExchangeRate],
[FromDate],
[ToDate]
)
SELECT [CurrencyCode],
ISNULL([ExchangeRate],0) AS [ExchangeRate],
ISNULL(LAG([ToDate]) OVER (ORDER BY [DW_Id]),DATEADD(yy, DATEDIFF(yy, 0, [ToDate]), 0)) AS [FromDate],
[ToDate]
FROM [ExchangeRates]
WHERE [CurrencyCode] = @iCurrencyCode
SET @iCurrencyCode = @iCurrencyCode + 1
END
SELECT * FROM #TempTable
DROP TABLE #TempTable
END
In there I make a #TempTable, which I use to make a while loop.
To use my stored procedure I had a custom table with a custom table insert statement.

DECLARE @1 as INT = (SELECT MIN([CurrencyCode]) FROM [ExchangeRates_V])
DECLARE @2 as INT = (SELECT MAX([CurrencyCode]) FROM [ExchangeRates_V])
DECLARE @Temp TABLE
(
[CurrencyCode] int,
[ExchangeRate] decimal(38,3),
[FromDate] datetime,
[ToDate] datetime
)
INSERT @Temp EXEC [DSA].[spExchangeRates]
@iCurrencyCode = @1,
@iMaxCurrencyCode = @2
SELECT [CurrencyCode],
[ExchangeRate],
[DateValue] AS [Date]
FROM @Temp
LEFT JOIN [Date_V] AS D ON [FromDate] <= D.[DateValue] AND [ToDate] > D.[DateValue]
This also uses a temp table, here I use @Temp instead. I start by declaring it and then using that to run my stored procedure.
I don't think there is any limits to where you can use these, but I do believe I had some issues using them in a view, but test out your scripts in management studio, if they work there you can make them work in our program as well.
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.