Problem with Drop & reccreate a staging table

Question

Problem with Drop & reccreate a staging table

999baz

Ten Centuries

Points: 1053
More actions
October 20, 2009 at 8:23 pm

#227278

Well this is what i am tring to do simply drop a tbale if it EXISTS and recreate it?
But i get this meeesage:-
"There is already an object named 'TableStage' in the database."
However, whe i repalce EXIST with NOT EXISTS it works can some explain to me what's happenig here.
/*This SQL code checks for the presence of the destination table.
If the table does not exist, it is created.
If the table exists, it is dropped and re-created.
Without this package step, the same data from the source table is appended
to the destination table every time the package is run.
*/
IF EXISTS (SELECT * from sysobjects
WHERE id = object_id(N'[ANZ].[dbo].[TableStage] ') AND
--type in (N'U'))
OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP Table [ANZ].[dbo].[TableStage]
GO
CREATE TABLE [dbo].[TableStage](
[ID] [varchar](20) NOT NULL,
[Src] [varchar](10) NOT NULL,
[Sector] [varchar](15) NOT NULL,
[Title] [varchar](50) NULL,
[Surname] [varchar](50) NULL,
[Firstname] [varchar](50) NULL,
CONSTRAINT [PK_TableStage] PRIMARY KEY CLUSTERED
(
[ID] ASC,
[Src] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

That thing that seems strange is that you are checking the property of if this is a user table. Is the type commented out? Hard to tell from the formatting. All I can think of is the IF is not correctly finding the table and the NOT EXISTS is returning true where the EXISTS returns false.

I've typically just checked for the name from sysobjects and then dropped the table. If it's a proc of view, the DROP TABLE won't work anyway.

arun.sas SSChampion Points: 11831 More actions · Answer 2

999baz (10/20/2009)
IF EXISTS (SELECT * from sysobjects
WHERE id = object_id(N'[ANZ].[dbo].[TableStage] ') AND
--type in (N'U'))
OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP Table [ANZ].[dbo].[TableStage]
GO

Hi,

Id is not your table, mentioned in the OBJECTPROPERTY

Try with the name of the table like

IF EXISTS (SELECT * from sysobjects

WHERE id = object_id(N'[ANZ].[dbo].[TableStage]') AND

--type in (N'U'))

OBJECTPROPERTY(object_id(N'[ANZ].[dbo].[TableStage] ') ,N'IsUserTable') = 1)

DROP Table [ANZ].[dbo].[TableStage]

GO

999baz Ten Centuries Points: 1053 More actions · Answer 3

OBJECTPROPERTY((N'[DatabaseName].[dbo].[TableStage]') , N'IsUserTable') = 1)

nope didn't work.

Msg:-

Conversion failed when converting the nvarchar value '[DatabaseName].[dbo].[TableStage]' to data type int.

Msg 2714, Level 16, State 6, Line 2

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

999baz Ten Centuries Points: 1053 More actions · Answer 4

~~OBJECTPROPERTY((N'[DatabaseName].[dbo].[TableStage]') , N'IsUserTable') = 1)~~

~~nope didn't work.~~

~~Msg:-~~

~~Conversion failed when converting the nvarchar value '[DatabaseName].[dbo].[TableStage]' to data type int.~~

~~Msg 2714, Level 16, State 6, Line 2~~

~~There is already an object named 'TableStage' in the database.~~

999baz Ten Centuries Points: 1053 More actions · Answer 5

sorry this didnt' work.

OBJECTPROPERTY(object_id(N'[DatabseName].[dbo].[TableStage]') , N'IsUserTable') = 1)

Msg:-

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

The answer is simple on this one... don't use a permanent table as a staging table... use a Temp Table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

999baz Ten Centuries Points: 1053 More actions · Answer 7

OK.

BUt what if i wanted to drop & recreate a non temp table? How would i do that? is my current approach incorrect.

I am actually trying to integrate this step in SSIS using sql task -

Can u suugest how i can drop this table and recreate it so it's rady for next bit of processing?

Cheers

Baz

999baz Ten Centuries Points: 1053 More actions · Answer 8

Well this is what i have done, dumped the following code (completed the create table script ofcourse) in Execute SQL task in a BIDS package. WORK S OK FOr me.

Thank you to those who tried to assist.Cheers

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableStage]') AND type in (N'U'))

DROP TABLE [dbo].[TableStage]

GO

USE [ABM]

GO

/****** Object: Table [dbo].[TableStage] Script Date: 10/21/2009 16:41:14 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING OFF

GO

CREATE TABLE [dbo].[TableStage]

/* Here i go on and recreate the table*/

GO