CODEWITHSUNDEEP
t-sqlsql-server-2005temp-tables
Matthew Verstraete
Matthew Verstraete

Reputation: 6781

Only 1 of two temp tables getting dropped even though the code to drop both is the same

I have a block of code that checks of a temp table exists, drops it if it does, then creates the table again filling it with data. This code block is then ran again but for a different table.

When I run the script I get the error

There is already an object named '#Adjustments' in the database.

even though I have a drop command for it. The script is dropping the first table with no problems so I am not sure why it is not dropping the second table.

--Create a temp table with the total Receipts
IF (OBJECT_ID('tempdb..#Receipts') IS NOT NULL)
BEGIN
    DROP TABLE #Receipts;
END

SELECT  TerminalId,
        ProductId,
        SUM(GrossGallons) AS [GrossGallons],
        SUM(NetGallons) AS [NetGallons]
INTO #Receipts
FROM dbo.ReceiptAdjustment WITH (NOLOCK)
WHERE IsReceipt = 1
AND ReceiptAdjustmentDate BETWEEN @StartDate AND @EndDate
GROUP BY    TerminalId,
            ProductId,
            ReceiptAdjustmentDate;

--Create a temp table wth the total Adjustments
IF (OBJECT_ID('tempd..#Adjustments') IS NOT NULL)
BEGIN
    DROP TABLE #Adjustments;
END

SELECT  TerminalId,
        ProductId,
        SUM(GrossGallons) AS [GrossGallons],
        SUM(NetGallons) AS [NetGallons]
INTO #Adjustments
FROM dbo.ReceiptAdjustment WITH (NOLOCK)
WHERE IsReceipt = 0
AND ReceiptAdjustmentDate BETWEEN @StartDate AND @EndDate
GROUP BY    TerminalId,
            ProductId,
            ReceiptAdjustmentDate;

Upvotes: 0

Views: 49

Answers (1)

Horaciux
Horaciux

Reputation: 6477

There is a typo

change 

IF (OBJECT_ID('tempd..#Adjustments') IS NOT NULL)

with

IF (OBJECT_ID('tempdb..#Adjustments') IS NOT NULL)

Upvotes: 1

Related Questions