OK, to more clearly show what I'm seeing I wrote the following code.  I just find it odd that in the first example you can see that the table already exists in this context.  I would expect the expression to throw an exception but it doesn't.  In looking at the system output you can see that a new temp table is created and populated.  

As Luis noted earlier, this is explained in BOL (see his link) in the context of 2 stored procedures creating temp tables with the same name.  


IF (SELECT OBJECT_ID('tempdb..#tmp')) IS NOT NULL
DROP TABLE #tmp

-- Create an empty table holding ints
SELECT TOP 0 ID INTO #tmp from sysobjects

-- Print the actual name and ID of this object
declare @nm varchar (500)
SELECT @nm = 'ID-' + convert(varchar, OBJECT_ID('tempdb..#tmp')) +
   ' ' + name from tempdb.dbo.sysobjects WHERE ID = OBJECT_ID('tempdb..#tmp')
print(@nm)

print('Test One')
-- Populate the table and show that in the executed SQL we're pointing to the same table
-- I would expect this to throw an exception since the table already exists
exec sp_executesql N'
declare @nm varchar (500)
SELECT @nm = ''ID-'' + convert(varchar, OBJECT_ID(''tempdb..#tmp'')) +
   '' '' + name from tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(''tempdb..#tmp'')
print(@nm)

SELECT TOP 10 ID INTO #tmp from sysobjects

print(''After insterting records'')
SELECT @nm = ''ID-'' + convert(varchar, OBJECT_ID(''tempdb..#tmp'')) +
   '' '' + name from tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(''tempdb..#tmp'')
print(@nm)
'

-- Note that the sys out shows 10 rows were affected but the table is empty
SELECT * FROM #tmp

print('Test Two')
-- Populate the table and show that in the executed SQL we're pointing to the same table
-- This method actually updates the temp table
exec sp_executesql N'
declare @nm varchar (500)
SELECT @nm = ''ID-'' + convert(varchar, OBJECT_ID(''tempdb..#tmp'')) +
   '' '' + name from tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(''tempdb..#tmp'')
print(@nm)

INSERT #tmp (ID) SELECT TOP 10 ID from sysobjects
'

-- Note that the sys out shows 10 rows were affected and the table is not empty
SELECT * FROM #tmp