I have a query where I insert some values from a table:
SELECT ID, NAME INTO #tmpTable1
FROM TableOriginal
First execution is fine, if I press F5(Run) in MSSMS (Microsoft Sql Server Management Studio), the error occured:
Msg 2714, Level 16, State 6, Line 4
There is already an object named '#tmpTable1' in the database.
Good. I decided to check before insert data from TableOriginal to #tmpTable1 using:
IF OBJECT_ID('tempdb.#tmpTable1') IS NOT NULL
DROP TABLE #tmpTable1
Not working, the error shows again as above.
I saw in tempdb database the following temporary table name:
dbo.#tmpTable1__________________0000007
Why? Every time when create a temporary table (using first query), the table name will be generated automatically in MSSMS ?
How to remove the existing temporary table to do a new table with new values ?