IF table exists, DROP TABLE then CREATE TABLE - script not working

Question

IF table exists, DROP TABLE then CREATE TABLE - script not working

jhager

SSCrazy

Points: 2310
More actions
November 1, 2013 at 8:53 am

#285472

Hi all,
This code to change a column name is not working and I'm not able to figure out why:
IF OBJECT_ID(N'dbo.mtzcus_2014_CoreMenuReportCriteria', N'U') IS NOT NULL
BEGIN;
DROP TABLE dbo.mtzcus_2014_CoreMenuReportCriteria;
CREATE TABLE dbo.mtzcus_2014_CoreMenuReportCriteria
(
MeasureType VARCHAR(20) NOT NULL
,MeasureOrder TINYINT NOT NULL
,ObjectiveNumber VARCHAR(3) NOT NULL
,ObjectiveName VARCHAR(100)
,CriteriaCertificationNumber VARCHAR(50)
,ExclusionDescription VARCHAR(100)
,Threshold2014Stage2 DECIMAL(3, 0)
,Threshold2014Stage1 DECIMAL(3, 0)
,CONSTRAINT mtzcus_2014_CoreMenuReportCriteria_PK PRIMARY KEY CLUSTERED
( MeasureType, MeasureOrder, ObjectiveNumber )
);
END;
Previous version was:
CREATE TABLE dbo.mtzcus_2014_CoreMenuReportCriteria
(
MeasureType VARCHAR(20) NOT NULL
,MeasureOrder TINYINT NOT NULL
,ObjectiveNumber TINYINT NOT NULL
,ObjectiveName VARCHAR(100)
,CriteriaCertificationNumber VARCHAR(50)
,ExclusionDescription VARCHAR(100)
,Threshold2014Stage2 DECIMAL(3, 0)
,Threshold2011Or2013Stage1 DECIMAL(3, 0)
,CONSTRAINT mtzcus_2014_CoreMenuReportCriteria_PK PRIMARY KEY CLUSTERED
( MeasureType, MeasureOrder, ObjectiveNumber )
);
The error message is:
Msg 207, Level 16, State 1, Line 32
Invalid column name 'Threshold2014Stage1'.
This seems to be because the DROP TABLE never executes or fails (no error message).
Any help is appreciated.
~ Jeff

Viewing 15 posts - 1 through 14 (of 14 total)

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

jhager SSCrazy Points: 2310 More actions · Answer 1

I found a MS reference that states:

"DROP TABLE and CREATE TABLE should not be executed on the same table in the same batch. Otherwise an unexpected error may occur."

and also:

"A table cannot be changed and then the new columns referenced in the same batch."

Now I just have to figure out how to accomplish this given the restrictions.

Thanks for looking, though.

~ Jeff

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

It is quite simple. Don't do this in the same batch.

IF OBJECT_ID(N'dbo.mtzcus_2014_CoreMenuReportCriteria', N'U') IS NOT NULL

DROP TABLE dbo.mtzcus_2014_CoreMenuReportCriteria;

CREATE TABLE dbo.mtzcus_2014_CoreMenuReportCriteria

(

MeasureType VARCHAR(20) NOT NULL

,MeasureOrder TINYINT NOT NULL

,ObjectiveNumber VARCHAR(3) NOT NULL

,ObjectiveName VARCHAR(100)

,CriteriaCertificationNumber VARCHAR(50)

,ExclusionDescription VARCHAR(100)

,Threshold2014Stage2 DECIMAL(3, 0)

,Threshold2014Stage1 DECIMAL(3, 0)

,CONSTRAINT mtzcus_2014_CoreMenuReportCriteria_PK PRIMARY KEY CLUSTERED

( MeasureType, MeasureOrder, ObjectiveNumber )

);

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Why drop and create a table to change a column name? Why not use sp_rename?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jhager SSCrazy Points: 2310 More actions · Answer 4

The script is used to deploy code and data objects to our customers, and contains several objects entirely contained within a TRY/CATCH block. The script is designed to be run as a whole whether or not the customer has run it before.

The purpose of the script is not to rename the column, but rather to build the necessary objects. We just want to change the name as part of the process.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Then you're going to have to use dynamic SQL.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jhager SSCrazy Points: 2310 More actions · Answer 6

I was hoping to avoid that, but ya gotta do what ya gotta do.

Thanks Gail,

~ Jeff

jhager SSCrazy Points: 2310 More actions · Answer 7

This is really beginning to frustrate me.

IF OBJECT_ID(N'dbo.mtzcus_2014_CoreMenuAuditDetails', N'U') IS NOT NULL

AND EXISTS ( SELECT TOP 1

'X'

FROM

sys.columns

WHERE

name = N'Threshold2011Or2013Stage1'

AND [object_id] = OBJECT_ID(N'dbo.mtzcus_2014_CoreMenuAuditDetails') )

BEGIN;

/* Rename two columns */

EXECUTE sys.sp_rename

'dbo.mtzcus_2014_CoreMenuAuditDetails.Threshold2011Or2013Stage1'

,'dbo.mtzcus_2014_CoreMenuAuditDetails.Threshold2014Stage1';

EXECUTE sys.sp_rename

'dbo.mtzcus_2014_CoreMenuAuditDetails.MetThreshold2011Or2013Stage1'

,'dbo.mtzcus_2014_CoreMenuAuditDetails.MetThreshold2014Stage1';

END;

ELSE

IF OBJECT_ID(N'dbo.mtzcus_2014_CoreMenuAuditDetails', N'U') IS NULL

BEGIN;

EXECUTE(N'

CREATE TABLE dbo.mtzcus_2014_CoreMenuAuditDetails

(

AuditID UNIQUEIDENTIFIER NOT NULL

,MeasureName VARCHAR(100)

,CriteriaCertificationNumber VARCHAR(50)

,ObjectiveNumber VARCHAR(3)

,ObjectiveName VARCHAR(100)

,Denominator DECIMAL

,Numerator DECIMAL

,Percentage DECIMAL(5, 2)

,Threshold2014Stage2 DECIMAL(3, 0)

,MetThreshold2014Stage2 CHAR(1)

,Threshold2014Stage1 DECIMAL(3, 0)

,MetThreshold2014Stage1 CHAR(1)

,ExclusionDescription VARCHAR(100)

,MetExclusion CHAR(1)

,SelfAttestation CHAR(1)

,CONSTRAINT mtzcus_2014_CoreMenuAuditDetailsFK FOREIGN KEY ( AuditID ) REFERENCES dbo.mtzcus_2014_CoreMenuAuditRun ( AuditID )

);');

When the table does NOT exist, the last section of code "executes" (I put a SELECT 'X' before the dynamic statement, which is displayed) but the table is NOT created (I immediately run SELECT TOP 1 * FROM dbo.mtzcus_2014_CoreMenuAuditDetails and get error "Invalid object name 'dbo.mtzcus_2014_CoreMenuAuditDetails'."

Am I missing something obvious here, or maybe something not so obvious?

Thanks,

Jeff

Abu Dina SSChampion Points: 14155 More actions · Answer 8

A little bit of formatting + fix 😉

You don't need to include the schema name when you call the OBJECT_ID function:

OBJECT_ID(N'dbo.mtzcus_2014_CoreMenuAuditDetails', N'U')

should be just

OBJECT_ID(N'mtzcus_2014_CoreMenuAuditDetails', N'U')

IF OBJECT_ID(N'mtzcus_2014_CoreMenuAuditDetails', N'U') IS NOT NULL

AND EXISTS ( SELECT TOP 1 FROM sys.columns

WHERE name = N'Threshold2011Or2013Stage1'

AND [object_id] = OBJECT_ID(N'mtzcus_2014_CoreMenuAuditDetails') )

BEGIN;

/* Rename two columns */

EXECUTE sys.sp_rename

'dbo.mtzcus_2014_CoreMenuAuditDetails.Threshold2011Or2013Stage1'

,'dbo.mtzcus_2014_CoreMenuAuditDetails.Threshold2014Stage1';

EXECUTE sys.sp_rename

'dbo.mtzcus_2014_CoreMenuAuditDetails.MetThreshold2011Or2013Stage1'

,'dbo.mtzcus_2014_CoreMenuAuditDetails.MetThreshold2014Stage1';

END;

ELSE IF OBJECT_ID(N'mtzcus_2014_CoreMenuAuditDetails', N'U') IS NULL

BEGIN;

EXECUTE(N'

CREATE TABLE dbo.mtzcus_2014_CoreMenuAuditDetails

(

AuditID UNIQUEIDENTIFIER NOT NULL

,MeasureName VARCHAR(100)

,CriteriaCertificationNumber VARCHAR(50)

,ObjectiveNumber VARCHAR(3)

,ObjectiveName VARCHAR(100)

,Denominator DECIMAL

,Numerator DECIMAL

,Percentage DECIMAL(5, 2)

,Threshold2014Stage2 DECIMAL(3, 0)

,MetThreshold2014Stage2 CHAR(1)

,Threshold2014Stage1 DECIMAL(3, 0)

,MetThreshold2014Stage1 CHAR(1)

,ExclusionDescription VARCHAR(100)

,MetExclusion CHAR(1)

,SelfAttestation CHAR(1)

,CONSTRAINT mtzcus_2014_CoreMenuAuditDetailsFK FOREIGN KEY ( AuditID ) REFERENCES dbo.mtzcus_2014_CoreMenuAuditRun ( AuditID )

);');

END

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

jhager SSCrazy Points: 2310 More actions · Answer 9

Other than missing the END statement (which exists later on in my script), I'm still not seeing anything incorrect.

Abu Dina SSChampion Points: 14155 More actions · Answer 10

Sorry! I have updated my previous comment which explains the changes that I made!

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

Sean Pearce SSCoach Points: 15999 More actions · Answer 11

Abu Dina (11/4/2013)
You don't need to include the schema name when you call the OBJECT_ID function:
OBJECT_ID(N'dbo.mtzcus_2014_CoreMenuAuditDetails', N'U')
should be just
OBJECT_ID(N'mtzcus_2014_CoreMenuAuditDetails', N'U')

That's incorrect. You should include the schema name when you call the OBJECT_ID function although you don't have to.

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Sean Pearce SSCoach Points: 15999 More actions · Answer 12

jhager (11/4/2013)
Other than missing the END statement (which exists later on in my script), I'm still not seeing anything incorrect.

The code works for me. The table is created correctly.

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

jhager SSCrazy Points: 2310 More actions · Answer 13

I think I found the problem -- I was running the script in one session, then checking the results in another.

~ Jeff

Jovan Popovic SSC Enthusiast Points: 191 More actions · Answer 14

In SQL Server 2016 you can use DROP TABLE IF EXISTS table_name.

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016.aspx