SQL Error: "There is already an object named XXXX in the database"

Question

Hi Guys,

Here's my query. What I want to do is run this query every week so table PlanFinder.InvalidAwps will have new records. But when I run the query it gives me this error :

There is already an object named 'InvalidAwps' in the database. 

I can't change the table name. It has to remain the same. So how can I run this query every week keeping table name as it is?

Appreciate any help. Thanks Nick

-------------------------------------
IF  EXISTS (SELECT * FROM sys.objects  
WHERE object_id = OBJECT_ID(N'[PlanFinder].[InvalidAwps]')  
AND type in (N'U')) 
BEGIN 
      DROP TABLE [PlanFinder].[InvalidAwps] 
END 

SELECT DISTINCT P.Ndc Ndc, A.Price AwpUnitCost INTO PlanFinder.InvalidAwps
FROM
    PlanFinder.PlanFinder.HpmsFormulary P
    LEFT JOIN (SELECT Ndc, Price FROM MHSQL01D.Drug.FdbPricing.vNdcPrices 
               WHERE PriceTypeCode = '01' AND CurrentFlag = 1) A
ON P.Ndc = A.Ndc 
WHERE (A.Ndc IS NULL OR A.Price <= 0 OR A.Price IS NULL)
AND p.Ndc IS NOT NULL 
----------------------------------------------

Answer 1

The first part of your SQL checks for a table that exists in the dbo schema called YourTableName I'm guessing this should be InvalidAwps? You need to change the schema and table name to match [PlanFinder].[InvalidAwps] and you shouldn't have any problems.

As it stands you will never drop table as the schema and/or table name don't match.

Woah! It's like a completely different question now...

Maybe you need a GO before you start your Select statement

-------------------------------------
IF  EXISTS (SELECT * FROM sys.objects  
WHERE object_id = OBJECT_ID(N'[PlanFinder].[InvalidAwps]')  
AND type in (N'U')) 
BEGIN 
      DROP TABLE [PlanFinder].[InvalidAwps] 
END 
Go

Answer 2

You could try the simple version:

if object_id('[PlanFinder].[InvalidAwps]') is not null
    drop table [PlanFinder].[InvalidAwps]

Another option is to place go between the table drop and the insert into.

drop table [PlanFinder].[InvalidAwps]
go
select ... into [PlanFinder].[InvalidAwps]

SQL Server parses the SQL before it executes it, and the table still exists during parsing.

Answer 3

Why go through the work of dropping and recreating the table every time? Instead, create the table once and then going forward:

truncate table [PlanFinder].[InvalidAwps]
go

insert into [PlanFinder].[InvalidAwps]
    (Ndc, AwpUnitCost)
    SELECT DISTINCT P.Ndc Ndc, A.Price AwpUnitCost
    FROM
        PlanFinder.PlanFinder.HpmsFormulary P
        LEFT JOIN (SELECT Ndc, Price FROM MHSQL01D.Drug.FdbPricing.vNdcPrices 
                   WHERE PriceTypeCode = '01' AND CurrentFlag = 1) A
    ON P.Ndc = A.Ndc 
    WHERE (A.Ndc IS NULL OR A.Price <= 0 OR A.Price IS NULL)
    AND p.Ndc IS NOT NULL