Add a column to a table and then "insert into" is invalid in a stored procedure

Question

I have a problem similar to this SO question.

I have three tables that are related with an identity column as primary key. And I need to clone them into a new database with new identity values.

These are the tables where I'm going to copy the data, but the relationship is the same in the source database.

CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            IDENTITY (1, 1) NOT NULL,
    [Serial]            NVARCHAR (20)  NOT NULL,
    [...]
)
CREATE TABLE [dbo].[Aggregation] (
    [AggregationId] INT           NOT NULL,
    [Created]       NVARCHAR (50) NOT NULL,
    CONSTRAINT [PK_AGGREGATIONS] PRIMARY KEY CLUSTERED ([AggregationId] ASC),
    CONSTRAINT [FK_Aggregation_Code]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Code] ([CodeId])
)
CREATE TABLE [dbo].[AggregationChildren] (
    [AggregationChildrenId] INT NOT NULL,
    [AggregationId]         INT NOT NULL,
    [Position]              INT NOT NULL,
    CONSTRAINT [PK_AGGREGATION_CHILDS] PRIMARY KEY CLUSTERED ([AggregationChildrenId] ASC),
    CONSTRAINT [FK_AggregationChildren_Code]
           FOREIGN KEY ([AggregationChildrenId])
            REFERENCES [dbo].[Code] ([CodeId]),
    CONSTRAINT [FK_AggregationChildren_Aggregation]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Aggregation] ([AggregationId]) ON DELETE CASCADE
)

There is a master table, Code, and the other tables related to the master by its primary key Code.CodeId.

I'm going to add an auxiliary column, with the value of the primary key in the source database, to make the conversion in tables Aggregation and AggregationChildren.

That process work in SQL Server Management Studio, but when I try to use it in a procedure stored it complains about that the auxiliary column doesn't exist.

The stored procedure is like this pseudo code:

// Alter table to add the auxiliary column
// Insert into with select and inserting into the auxiliary column
// Alter table to drop the auxiliary column

The stored procedure has an error because I'm inserting in a column that doesn't exist.

Do you know if there is another solution to this problem using an auxiliary column?

Answer 1

You cannot add a column and use it in the same batch; in Management Studio you are able to insert "go" between them so it works, in stored procedure you can insert your table within EXEC like this:

exec('insert table..(old_cols, new_col)...')

The table structure is checked if a table exists so the procedure cannot be compiled when you use a new column, but it will not check a dynamic code that compiles on its own and when it will be compiled the column will be already there