Select * into not working

Toad for SQL Server
1

I've tried to run the following, but it errors out saying
There is already an object named 'PNV_MMA_Pharm_Data_Prev' in the database.
I also tried to TRUNCATE THE "Prev" table, but still get the same error.
Do I need to DELETE the "Prev" table then do the SELECT * INTO ?

IF OBJECT_ID ('MDCOutEd.PNV_MMA_Pharm_Data_Prev') IS NOT NULL
DROP TABLE [AdHocData].MDCOutEd.PNV_MMA_Pharm_Data_Prev

SELECT *
INTO [AdHocData].MDCOutEd.PNV_MMA_Pharm_Data_Prev
FROM [AdHocData].MDCOutEd.PNV_MMA_Pharm_Data_Curr

2

I'm personally not an expert on SQL Server syntax, but you may need to embrace your DROP logic in a BEGIN/END block. e.g. to define a transaction after which the database can commit your changes/drops/etc. I'd test our your logic in chunks.

Is the object_id indeed null/not null?
If object exists, when you execute your DROP statement separately, does it work?

If you get desired results for the individual code chunks, then put them together and see if it still works as desired.

3

Hi Gary,

The Drop and Truncate work but the Select * Into is what is failing.

Timestamp Message
5/23/2022 2:52:47 PM Executing script: TRUNCATE TABLE [AdHocData].MDCOutEd.PNV_MMA_Pharm_Data_Prev

5/23/2022 2:52:48 PM Script execution completed.

Execution SQL: SELECT *
INTO [AdHocData].MDCOutEd.PNV_MMA_Pharm_Data_Prev
FROM [AdHocData].MDCOutEd.PNV_MMA_Pharm_Data_Curr

SQL Server Database Error|

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

4

You are qualifying the database name in the select-into and dtop statements, but not in the object_id parameter, so it is possible that you are querying object_id in a different database.

There are a few options for addressing this:

  1. You can set the database before running any of those statements:
USE [AdHocData]
  1. You can add the database name to the object_id function call:
IF OBJECT_ID ('[AdHocData].MDCOutEd.PNV_MMA_Pharm_Data_Prev') IS NOT NULL
DROP TABLE [AdHocData].MDCOutEd.PNV_MMA_Pharm_Data_Prev
  1. If you are using SQL Server 2016 or newer, you can use replace the entire conditional drop clause with DROP TABLE IF EXISTS:
DROP TABLE IF EXISTS [AdHocData].MDCOutEd.PNV_MMA_Pharm_Data_Prev

Hope that helps