Can i execute my code without error.

Vinay Talapaneni

6y741

if exists(select top 1 1 from sys.tables where name = '#table1')

drop table #table1

create table #table1(id int)

if exists(select top 1 1 from sys.tables where name = '#table1')

drop table #table1

create table #table1(id int)

select * from #table1

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Laxmidhar Sahoo6y ago
hi
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
BEGIN
    DROP TABLE #TempTable
END

CREATE TABLE #TempTable ( ID INT, Name VARCHAR(100) )
GO

or


IF OBJECT_ID('TempDB.dbo.#TempTable') IS NOT NULL
BEGIN
  PRINT '#TempTable Temporary Table Exists'
END
GO
Thanks and regards
Vinay Talapaneni6y ago
when we creating temp tables it will store with a unique name like below.
#table1_____________________________________________________________________________________________________________0000000022EC.
if we execute below in tempdb we can drop and create the table.

if exists(select top 1 1 from sys.tables where replace(left(name,(charindex('_',name))),'_','') = '#table1')
drop table #table1
create table #table1(id int)
go
if exists(select top 1 1 from sys.tables where replace(left(name,(charindex('_',name))),'_','') = '#table1')
drop table #table1
create table #table1(id int)
go
select * from #table1
go
Salman Beg6y ago
Hey. When you are creating a temporary table then it is exists under the tempdb. But while you are dropping you need to use the master database and then you need to execute the drop command.
You can follow this link for example,
https://www.c-sharpcorner.com/UploadFile/rohatash/temporary-tables-in-sql-server-2012/
Vinay Talapaneni6y ago
hello, beg, thank you for responding. I am getting an error.
Msg 2714, Level 16, State 1, Line 6
There is already an object named '#table1' in the database.
I want to execute command without using like operator.
Salman Beg6y ago
Hey.
Are you getting any error?
Also share some more detail like what do you want.
Thanks.