How to Get Rid of #Temp Tables from the Query

Drop all temporary tables for an instance

The point of temporary tables is that they are.. temporary. As soon as they go out of scope

#temp create in stored proc : stored proc exits
#temp created in session : session disconnects
##temp : session that created it disconnects

The query disappears. If you find that you need to remove temporary tables manually, you need to revisit how you are using them.

For the global ones, this will generate and execute the statement to drop them all.

declare @sql nvarchar(max)
select @sql = isnull(@sql+';', '') + 'drop table ' + quotename(name)
from tempdb..sysobjects
where name like '##%'
exec (@sql)

It is a bad idea to drop other sessions' [global] temp tables though.

For the local (to this session) temp tables, just disconnect and reconnect again.

How to get rid of #temp tables from the query

if you are using MSSql, use CTE

;with cte1 as (
    select {some columns} from {some tables} where {conditions1}
), 
cte2 as (
    select {some other columns} from {some tables} where {conditions2}
),
cte3 as (
    select {some other columns} from {some tables} where {conditions3}
)
select {some columns from all ctes} from cte1, cte2, cte3 where {conditions}

this should run faster as there is no need to insert data into temp table.

another advantage of avoiding temp table is, using temp table sometimes has really bad impact on performances, as there is only one tempdb for entire sql server and heavily use tempdb, may block other queries. just google temp table and performance impacts, you will find a lots of article on this topic

How to remove temp tables from logic in stored procedure query

In addition to Dale's sub-query, you can use a CTE

with cte1 as (
SELECT    
    Column_1,
    Column_2,
    Column_3    
FROM TABLE_1    
WHERE Column_1 = 1
),  cte2 as (
SELECT    
    Column_1,
    Column_4,
    Column_5,
    Column_6    
FROM TABLE_2    
WHERE Column_4 = 4    
)
SELECT
    A.Column_1,
    A.Column_2,
    A.Column_3,
    B.Column_4,
    B.Column_5,
    B.Column_6    
FROM cte2 B    
INNER JOIN cte1 A ON A.Column_1 = B.Column_1

How do I force a drop temp table command to fully drop the table?

In SSMS you can just add a GO statement to separate the code in 2 batches.
This way the drop is executed before the 2nd part of the script is checked for errors.

drop table if exists #DemoTable
GO

select column1 = '1'
      ,column2 = '2'
--      ,column3 = '3'
into #DemoTable

select column1
      ,column2
--      ,column3
from #DemoTable

Deleting Global Temporary Tables (##tempTable) in SQL Server

Local temporary tables are destroyed when you close your connection to SQL Server. There is no need to manually purge them under normal circumstances. If you maintain a persistent connection, or connection pooling, you may want to get in the habit of dropping temporary tables immediately after use.

Global temporary tables, on the other hand, since they are visible to all users in a given database, are destroyed along with the last connection which references them.

Drop temp table if it exists

Temp #Tables are created in tempdb. Try this:

IF OBJECT_ID('tempdb..#lu_sensor_name_19') IS NOT NULL 
BEGIN 
    DROP TABLE #lu_sensor_name_19 
END

CREATE TABLE #lu_sensor_name_19...

SQL Server 2016 added the ability to do the drop in one line:

DROP TABLE IF EXISTS #lu_sensor_name_19 

CREATE TABLE #lu_sensor_name_19...