How can I drop my temp table if it exits?

Question

I have to have a temp table to store some memberIds. I need to drop the table every time because I want its data always be fresh and updated. But I need it to be dropped before it being re-created every time. But it seems somehow SQL-server doesn't execute my whole script.

IF OBJECT_ID('tempdb.dbo.#available_memberIds', 'U') IS NOT NULL
BEGIN


  DROP TABLE #available_memberIds;
  CREATE TABLE #available_memberIds (
            memberId Int,);

        Insert into #available_memberIds SELECT memberId
        FROM tbl_attendancemembers
        WHERE memberId NOT IN
            (SELECT memberId 
             FROM tbl_attendanceSheet)

        UNION

        SELECT memberId
        FROM tbl_attendancemembers
        WHERE memberId IN
            (     SELECT memberId
                  FROM tbl_attendanceSheet
                  GROUP BY memberId, date
                  HAVING COUNT(*) <= 1)



END

When I Highlight the whole code and execute it, it says executing was successful. But when I select * from #available_memberIds it doesn't show any memberId which means it doesn't insert anything to the table. Although when I highlight only the Insert Into part individually and select * from #available_memberIds it shows the memberIds.

Don't I have to write something for the IF conditions else part? I copied everything except the drop part for the else part but when I execute whole code(containing the else part) it always says

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

Which is weird because when I highlight only the drop part and execute it, it says >Can not drop the table

'#available_memberIds', because it does not exist or you do not have permission.

And when I select * from #available_memberIds it says :

Invalid object name '#available_memberIds'.

IF OBJECT_ID('tempdb.dbo.#available_memberIds', 'U') IS NOT NULL
BEGIN


  DROP TABLE #available_memberIds;
  CREATE TABLE #available_memberIds (
            memberId Int,);
        BEGIN
        Insert into #available_memberIds SELECT memberId
        FROM tbl_attendancemembers
        WHERE memberId NOT IN
            (SELECT memberId 
             FROM tbl_attendanceSheet)
        UNION
        SELECT memberId
        FROM tbl_attendancemembers
        WHERE memberId IN
            (     SELECT memberId
                  FROM tbl_attendanceSheet
                  GROUP BY memberId, date
                  HAVING COUNT(*) <= 1)-- and date=@inputdate)
                  END


END
ELSE
BEGIN
                CREATE TABLE #available_memberIds (
            memberId Int,);
        BEGIN
        Insert into #available_memberIds SELECT memberId
        FROM tbl_attendancemembers
        WHERE memberId NOT IN
            (SELECT memberId 
             FROM tbl_attendanceSheet)
        UNION
        SELECT memberId
        FROM tbl_attendancemembers
        WHERE memberId IN
            (     SELECT memberId
                  FROM tbl_attendanceSheet
                  GROUP BY memberId, date
                  HAVING COUNT(*) <= 1)-- and date=@inputdate)
                  END

END

I'm writing SQL codes in the visual studio database manager

Is the behavior different when you use Management Studio instead of "visual studio database manager"? I think possibly you are getting errors due to re-established connections (basically "invalid object" means you have a new session). Other than testing in a local session, do you really have a need to drop the table before it exists? Under normal behavior if this is in a stored procedure or an ad hoc batch sent to the server, it shouldn't be possible for that temp table to already exist, since it is specific to a given session and each session gets its own, independent copy. — Aaron Bertrand, Jan 29 '19 at 18:08
@AaronBertrand I have not tried it in Management Studio. My knowledge about Management Studios is limited so I cant try it. — Danial Ranjbar, Jan 29 '19 at 18:10
@AaronBertrand Could you please reference me to a link or describe me, what do you mean by session? — Danial Ranjbar, Jan 29 '19 at 18:14
@AaronBertrand I need to drop the table every time because I want its data always be fresh and updated. — Danial Ranjbar, Jan 29 '19 at 18:22

Nick A · Answer 1 · 2019-01-30T17:41:15.297

You could use something like this. Then put your code After it. Make sure there are two dots between tempdb and your temp table. Then you don't need multiple statements, it will just delete it and re-create it every time.

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

...

    SELECT memberId
    INTO #available_memberIds
    FROM tbl_attendancemembers
    WHERE memberId NOT IN
        (SELECT memberId 
         FROM tbl_attendanceSheet)
    UNION
    SELECT memberId
    FROM tbl_attendancemembers
    WHERE memberId IN
        (     SELECT memberId
              FROM tbl_attendanceSheet
              GROUP BY memberId, date
              HAVING COUNT(*) <= 1)-- and date=@inputdate)
              END

How can I drop my temp table if it exits?

1 Answers1