question

apple avatar image
apple asked

Sp table already exists

I have a SP where I check for the existence of table and if exists drop it IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ Temptabels') AND type in (N'U')) DROP TABLE [dbo].[Temptable] then i create the table When I execute the SP I get an error that the table already exists Msg 2714, Level 16, State 6, Procedure name, Line 53 There is already an object named 'Temptable in the database. Any ideas on this thanks a lot
sql-server-2008
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

3 Answers

Grant Fritchey avatar image
Grant Fritchey answered
Assuming the sample code is the same as your real code, you've got some typos in the check for the object existence. Modifying [ Temptabels' to [Temptable]' and everything works.
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

apple avatar image apple commented ·
Thanks for the input that is not the real table name for posting the question I changed the name of the table Anyway I checked in my code first I drop the table, create table and then insert data into table is the order I am following Thanks
0 Likes 0 ·
jpatchak avatar image jpatchak commented ·
Are you creating the table with a CREATE statement or are you selecting into it to create it? Is there a conditional involved with how you're creating the table? I think we need to see the create code to troubleshoot.
0 Likes 0 ·
cmayorga avatar image
cmayorga answered
IF OBJECT_ID('tempdb..#temptable') IS NOT NULL DROP TABLE #temptable IF OBJECT_ID('tempdb..##temptable') IS NOT NULL DROP TABLE ##temptable IF OBJECT_ID('Local_TempTable') IS NOT NULL DROP TABLE Local_TempTable
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
If you have created the table TempTable using the # prefix to make use of a temporary table while code executes then this isnt created in the database where you code is executing, it is created in TempDB. @cymayorga has the code samples that you need to drop a #TempTable from TempDB.
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
I agree, but when you drop the table you have to test for it's OBJECT_ID in TempDB ... --This will fail USE [Scrap01] go CREATE TABLE #TestTable ( CompanyName varchar(100) ) IF OBJECT_ID('#TestTable') IS NOT NULL DROP TABLE #TestTable -- Whereas this will succeed USE [Scrap01] go CREATE TABLE #TestTable ( CompanyName varchar(100) ) IF OBJECT_ID('TEMPDB..#TestTable') IS NOT NULL DROP TABLE #TestTable
1 Like 1 ·
cmayorga avatar image cmayorga commented ·
@fatherjack thanks for the comment, but the tables that begin with # and ## is not necessary to specify the tempdb database, the default location is temdb create table #temptable ([id] int, [name] varchar(100), datein datetime null) IF OBJECT_ID('tempdb..#temptable') IS NOT NULL DROP TABLE #temptable
0 Likes 0 ·