"Invalid column name" error when calling insert after table created

Question

I'm developing SQL script, using SSMS, which makes some changes in database:

USE MyDatabase;

BEGIN TRANSACTION;

-- some statements

PRINT(N'#1');

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = N'dbo' AND TABLE_NAME = N'Table1' AND COLUMN_NAME = 'Table2_Id'))
BEGIN
    ALTER TABLE [dbo].[Table1] DROP CONSTRAINT [FK_Table1_Table2_Table2_Id];
    ALTER TABLE [dbo].[Table1] DROP COLUMN [Table2_Id];
    DROP TABLE [dbo].[Table2];

    PRINT(N'Table2 was dropped.');
END

PRINT(N'#2');

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = N'dbo' AND TABLE_NAME = N'Table2'))
BEGIN
    CREATE TABLE [dbo].[Table2]
    (
        [Id] INT NOT NULL PRIMARY KEY IDENTITY,
        [Number] INT NOT NULL UNIQUE,
        [Name] NVARCHAR(200) NOT NULL,
        [RowVersion] TIMESTAMP NOT NULL
    );
PRINT(N'Table2 was re-created.');
    INSERT INTO [dbo].[Table2]([Number], [Name]) VALUES(-1, N'Default value');
PRINT(N'Default value was inserted in Table2.');
END

-- some statements

COMMIT TRANSACTION;

If Table1 has a column, named Table2_Id, then database has two tables (Table1 and Table2) and a foreign key relationship between them. In that case, I need to:

• drop foreign key relationship FK_Table1_Table2_Table2_Id;
• drop foreign key column Table1.Table2_Id;
• drop Table2;
• re-create Table2, using new table schema;
• insert some default value in Table2.

When I'm trying to execute this script, I'm getting these errors:

Msg 207, Level 16, State 1, Line 262 Invalid column name 'Number'.
Msg 207, Level 16, State 1, Line 262 Invalid column name 'Name'.

Looks like SQL Server uses old schema for Table2 (which indeed hasn't these columns), but how is this possible, if the table has just created with new schema?

What am I doing wrong?

Server version is SQL Server 2012 (SP1) - 11.0.3128.0 (X64).

UPDATE.

I've added PRINT calls (see script above). There's nothing in message window, except error messages. So, the script isn't being executed... What's going on??

Answer 1

SQL Server tries to compile the whole batch. If the table already exists then it will compile according to the pre-existing definition. The statement referencing the new columns doesn't compile and so the batch never executes.

You need to group the statements using the new definition into a new batch. If you are running this in SSMS just insert a GO

USE MyDatabase;

BEGIN TRANSACTION;

-- some statements

PRINT(N'#1');

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = N'dbo' AND TABLE_NAME = N'Table1' AND COLUMN_NAME = 'Table2_Id'))
BEGIN
    ALTER TABLE [dbo].[Table1] DROP CONSTRAINT [FK_Table1_Table2_Table2_Id];
    ALTER TABLE [dbo].[Table1] DROP COLUMN [Table2_Id];
    DROP TABLE [dbo].[Table2];

    PRINT(N'Table2 was dropped.');
END

GO

PRINT(N'#2');

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = N'dbo' AND TABLE_NAME = N'Table2'))
BEGIN
    CREATE TABLE [dbo].[Table2]
    (
        [Id] INT NOT NULL PRIMARY KEY IDENTITY,
        [Number] INT NOT NULL UNIQUE,
        [Name] NVARCHAR(200) NOT NULL,
        [RowVersion] TIMESTAMP NOT NULL
    );
PRINT(N'Table2 was re-created.');
    INSERT INTO [dbo].[Table2]([Number], [Name]) VALUES(-1, N'Default value');
PRINT(N'Default value was inserted in Table2.');
END

COMMIT

Otherwise you could run the offending line in a child batch

    EXEC(N'INSERT INTO [dbo].[Table2]([Number], [Name]) VALUES(-1, N''Default value'');')