# SQL and Database: Unable to drop temp table?

**URL:** <https://cog.discourse.group/t/sql-and-database-unable-to-drop-temp-table/723>\
**Category:** useful info\
**Created:** [May 8, 2014, 3:19pm UTC](https://cog.discourse.group/t/sql-and-database-unable-to-drop-temp-table/723 "2014-05-08T15:19:39Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Nabiki](https://sea1.discourse-cdn.com/flex001/user_avatar/cog.discourse.group/nabiki/32/935_2.png) [@Nabiki](https://cog.discourse.group/u/Nabiki)\
**Post date:** [May 8, 2014, 3:19pm UTC](https://cog.discourse.group/t/sql-and-database-unable-to-drop-temp-table/723/1 "2014-05-08T15:19:39Z")

</div>

I’m trying to get a running total for a count, and I decided that the best way to do this would be to dump some counts into a temp table, then do a sum.

I have:

```
    DECLARE     
@startDate datetime = '3/3/2014 09:00',
@endDate datetime = '4/4/2014 23:00'

CREATE TABLE #SC
(Spec_Date DATETIME, Spec_Count INT)

SELECT CONVERT(VARCHAR , Sn_Date , 101) + ' ' + DATENAME(weekday , Sn_Date)+ ' ' + LEFT (CONVERT(VARCHAR , Sn_Date , 108), 5) 'Date/Time'
    ,COUNT (Sn_ID)
    INTO #SC
    FROM <table_name>
    WHERE Sn_Date BETWEEN @startDate AND @endDate)

SELECT #SC.[Date/Time]
,SUM(#SC.Spec_Count)
FROM #SC

DROP TABLE #SC

```

I get an error saying "Cannot drop the table ‘#SC’, because it does not exist or you do not have permission. If I try to run the code, it tells me that “There is already an object name ‘#SC’ in the database.”

I’ve tried exiting the application and opening it again, hoping that the temp table would go away when I ended the session, but it didn’t. I don’t see #SC in the table list…

---

<div class="post-metadata">

**Author:** ![dakboy](https://sea1.discourse-cdn.com/flex001/user_avatar/cog.discourse.group/dakboy/32/68_2.png) [@dakboy](https://cog.discourse.group/u/dakboy)\
**Post date:** [May 8, 2014, 3:33pm UTC](https://cog.discourse.group/t/sql-and-database-unable-to-drop-temp-table/723/2 "2014-05-08T15:33:15Z")

</div>

If I’m reading your query right, you can do it w/o the temp table - use nested queries instead.

```
SELECT [Date/Time]
	,sum(spec_count)
FROM (
	SELECT CONVERT(VARCHAR, Sn_Date, 101) + ' ' + DATENAME(weekday, Sn_Date) + ' ' + LEFT(CONVERT(VARCHAR, Sn_Date, 108), 5) AS 'Date/Time'
		,COUNT(Sn_ID) AS spec_count
	FROM < table_name >
	WHERE Sn_Date BETWEEN @startDate
			AND @endDate
	GROUP BY CONVERT(VARCHAR, Sn_Date, 101) + ' ' + DATENAME(weekday, Sn_Date) + ' ' + LEFT(CONVERT(VARCHAR, Sn_Date, 108), 5)
	) Counts
GROUP BY [Date/Time];

```

I don’t have a dataset handy that I can test this with easily to make sure it’s getting the sums you’re after.

---

<div class="post-metadata">

**Author:** ![Nabiki](https://sea1.discourse-cdn.com/flex001/user_avatar/cog.discourse.group/nabiki/32/935_2.png) [@Nabiki](https://cog.discourse.group/u/Nabiki)\
**Post date:** [May 8, 2014, 3:40pm UTC](https://cog.discourse.group/t/sql-and-database-unable-to-drop-temp-table/723/3 "2014-05-08T15:40:28Z")

</div>

The table dropped when I was playing with the code. :shrug: Not sure why it works now. I’ll play with the nested queries, @dakboy. Thanks!

Grrr… I’m trying to get a running total, but it doesn’t seem to be working. SUM is getting the same result as COUNT.

---

<div class="post-metadata">

**Author:** ![dakboy](https://sea1.discourse-cdn.com/flex001/user_avatar/cog.discourse.group/dakboy/32/68_2.png) [@dakboy](https://cog.discourse.group/u/dakboy)\
**Post date:** [May 8, 2014, 3:58pm UTC](https://cog.discourse.group/t/sql-and-database-unable-to-drop-temp-table/723/4 "2014-05-08T15:58:40Z")

</div>

For a running total, you want to use a Window Function if possible. See [http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver/10309947#10309947](http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver/10309947#10309947) if you’re running SQL Server 2012

If you’re on an older release, a recursive CTE I guess is the way to go. [http://stackoverflow.com/a/13744550/1324345](http://stackoverflow.com/a/13744550/1324345)

---

<div class="post-metadata">

**Author:** ![Brazil](https://avatars.discourse-cdn.com/v4/letter/b/b9bd4f/32.png) [@Brazil](https://cog.discourse.group/u/Brazil)\
**Post date:** [May 8, 2014, 4:04pm UTC](https://cog.discourse.group/t/sql-and-database-unable-to-drop-temp-table/723/5 "2014-05-08T16:04:53Z")

</div>

Go with what Dak said. It’ll be better.

The reason you were getting a failure before is because your SQL was creating #SC and then you were doing a “SELECT… into #SC”. That in itself will create #SC. Before the whole thing gets run, SQL server makes sure everything is copacetic, except the SELECT is going to try to create a table that you’ve already created. As a result, it didn’t do anything except return a somewhat misleading error message.

Instead of “SELECT … INTO”, you need to do a “INSERT #SC … SELECT”. Either that or don’t create #SC, just let the SELECT do it for you.