Cannot INSERT Into Newly Created Column

Posted on

Question :

I have a simple test table like this:

CREATE TABLE MyTable (x INT);

Within a transaction, I try to add a column and then insert into the newly created column:

BEGIN TRANSACTION;
    PRINT 'Adding column, ''SupplementalDividends'', to MyTable table.';

    ALTER TABLE MyTable
        ADD SupplementalDividends DECIMAL(18,6);

    PRINT 'Column added successfully....';

    PRINT 'Ready to INSERT into MyTable ...';

    INSERT INTO MyTable (x, SupplementalDividends)
        VALUES (1, 3.2);

    PRINT '**** CHANGES COMPLETE -- COMMITTING.';
COMMIT TRANSACTION;

The Problem is an error message when I run the above code:

Invalid column name 'SupplementalDividends'.

Why is this causing an error? If I add the column in a different batch, outside the transaction, it’ll work. My problem is that I want to add the column within the transaction. Why the error?

Answer :

Just want to clarify that this is a problem at run time, not compile time, and has nothing to do with the fact that they are in the same transaction. For example, let’s assume we have this table:

CREATE TABLE dbo.floob(a int);

The following batch parses successfully (compile time), but at runtime it gets the error you mention in the question:

BEGIN TRANSACTION;
  ALTER TABLE dbo.floob ADD b int;

  SELECT b FROM dbo.floob;
COMMIT TRANSACTION;

In a query editor in Management Studio, you can get around this easily, either by:

  1. Highlighting the first two lines, hitting execute, then highlighting the second two lines, and hitting execute; or,
  2. Putting a batch separator between them, like so:

    BEGIN TRANSACTION;
      ALTER TABLE dbo.floob ADD c int;
    
    GO
    
      SELECT c FROM dbo.floob;
    COMMIT TRANSACTION;
    

If you are running this from outside of SQL Server (e.g. sending a SQL batch from your application code), you can simply send the two batches separately in a similar manner, or if you absolutely need to send it as a single batch, you can use dynamic SQL (as in Gianluca’s answer) to defer name resolution.

It’s a binding issue. The code is bound to the metadata of the table at compile time and it is not late bound.
Try using EXEC and dynamic SQL to overcome this limitation:

BEGIN TRANSACTION;
    PRINT 'Adding column, ''SupplementalDividends'', to MyTable table.';

    ALTER TABLE MyTable
        ADD SupplementalDividends DECIMAL(18,6);

    PRINT 'Column added successfully....';

    PRINT 'Ready to INSERT into MyTable ...';
    EXEC('
    INSERT INTO MyTable (x, SupplementalDividends)
        VALUES (1, 3.2);
    ')

    PRINT '**** CHANGES COMPLETE -- COMMITTING.';
COMMIT TRANSACTION;

Another option is using a stored procedure to insert the data: late binding applies to stored procedure, but not to ad-hoc queries. Again, you would have to use dynamic SQL to create the procedure, but it could make it easier for you to pass parameters:

BEGIN TRANSACTION;
    PRINT 'Adding column, ''SupplementalDividends'', to MyTable table.';

    ALTER TABLE MyTable
        ADD SupplementalDividends DECIMAL(18,6);

    PRINT 'Column added successfully....';

    PRINT 'Ready to INSERT into MyTable ...';

    EXEC('
    CREATE PROCEDURE insData @p1 int, @p2 DECIMAL(18,6)
    AS
    BEGIN 
        INSERT INTO MyTable (x, SupplementalDividends)
        VALUES (@p1, @p2);
    END')

    EXEC InsData 1, 3.2;

    PRINT '**** CHANGES COMPLETE -- COMMITTING.';
COMMIT TRANSACTION;

A temporary stored procedure would work as well:

BEGIN TRANSACTION;
    PRINT 'Adding column, ''SupplementalDividends'', to MyTable table.';

    ALTER TABLE MyTable
        ADD SupplementalDividends DECIMAL(18,6);

    PRINT 'Column added successfully....';

    PRINT 'Ready to INSERT into MyTable ...';

    EXEC('
    CREATE PROCEDURE #insData @p1 int, @p2 DECIMAL(18,6)
    AS
    BEGIN 
        INSERT INTO MyTable (x, SupplementalDividends)
        VALUES (@p1, @p2);
    END')

    EXEC #InsData 1, 3.2;

    PRINT '**** CHANGES COMPLETE -- COMMITTING.';
COMMIT TRANSACTION;

I’m curious as to why you are altering a table and inserting a value into that column within the same transaction.

There is almost no chance that you’ll ever need to alter the table (the same exact way) again, unless it gets reverted every hour/day, so why do it within a transaction?

Your alter has not been committed yet, and therefore it is not found when you try to insert into it.

My advice is to separate your DDL and DML tasks (at least in separate transactions anyway).

Leave a Reply

Your email address will not be published.