ERROR:OBJECT ALREADY IN DATABASE

[kissarmi]

Hi,

I'm using a stored procedure with some SQL strings and EXEC statements. In the SQL string, I'm updating tables with code like this:

'SELECT * INTO #temp1 FROM '+@sql_location_from+' WHERE row_identifier = @parameter '+'INSERT INTO '+@sql_location_to+' SELECT * FROM #temp1'

I need to loop thru the table from within the SQL statement and update several rows. I get an error message saying:
"There is already an object named '#temp1' in the database."
Do I need to reset the #temp1 somehow? If so, how?

Thanks for any help.

 

[SamGarland]

Try using this before the SELECT ... INTO statement:

if OBJECT_ID('tempdb..#temp1') IS NOT NULL DROP TABLE #temp1

 

[SQLSister]

Don't use select into with temp tables. Create the temp table and then use the insert statement. I was going to ask why you were looping for an insert, but the content of your insert tells me you probably have a good reason to do so.