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

  • 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

  • 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

  • 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/

  • 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
  • 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.

  • 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
  • I was hoping to avoid that, but ya gotta do what ya gotta do.

    Thanks Gail,

    ~ Jeff

  • 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

  • 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

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

  • 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

  • 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]

  • 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]

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

    ~ Jeff

  • 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

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

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