CODEWITHSUNDEEP
reporting-servicesreport
user1777929
user1777929

Reputation: 797

How to add a Null Value record in a temporary table?

I have one table 'AgentEventStats' that has Agent IDs and their names. I use this table in a dataset that pulls up agent names to use a parameter for a Stats report. (This is to exclude those agents for my report).

However, I have to select an agent for that parameter or else my report won't work. That means that if I don't want to exclude any agents, (NULL value) I cannot run that report.

So, I thought, I would insert a Null value in a temp table that includes the AgentEventStats records.

What I tried:

    SELECT DISTINCT AgentReporting, AgentFirstName + ' ' + AgentLastName [AgentName]
    INTO #AgentStats    -- First, creating the temp table.
    FROM AgentEventStats
    WHERE MidnightStartDate >= dateadd(day, -60, getdate())--'2017-01-01'
    AND MidnightStartDate <  getdate() --dateadd(day,1,'2017-03-13')
    ORDER BY AgentName

    INSERT INTO #AgentStats (AgentReporting, AgentName)  --Then, inserting the Null value in that temp table.
    VALUES ('100', 'No Agents');

This doesn't work. I get an error message:

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

Someone suggested I use a Union All instead. Can someone guide me please?

Upvotes: 0

Views: 158

Answers (1)

R. Richards
R. Richards

Reputation: 25161

If you run the code like it is more than once in the same connection, you will get that error.

Quick fix: add DROP TABLE #AgentStats to the end of the script.

Then run just that line once (to drop the temp table).

Then you can run the whole script over and over again without error. Add in a SELECT * FROM #AgentStats before the drop to see what is in the table before you get rid of it.

Another way to do this is to first check to see if the temp table exists, then drop it before running the rest of the script. Like so:

IF OBJECT_ID('tempdb..#AgentStats') IS NOT NULL 
    DROP TABLE #AgentStats

SELECT DISTINCT...

If you do it this way, you won't need the DROP TABLE at the end any more.

Hope this helps you out.

EDIT

The UNION solution.

SELECT DISTINCT AgentReporting, AgentFirstName + ' ' + AgentLastName [AgentName]
FROM AgentEventStats
WHERE MidnightStartDate >= dateadd(day, -60, getdate())--'2017-01-01'
AND MidnightStartDate <  getdate() --dateadd(day,1,'2017-03-13')
UNION ALL -- added ALL based on comment
SELECT '100', 'No Agents'
ORDER BY 2;

Upvotes: 1

Related Questions