8

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?

6

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.

  • 1
    If this is a runtime error, I should be able to catch it, correct? I can't, though. – Andriy M Feb 1 '17 at 21:04
  • @AndriyM Can't catch all errors, no. And some occur somewhere between parse and execute, as this one shows. – Aaron Bertrand Feb 1 '17 at 21:06
  • @AndriyM Further to this, our Kiwi friend has an answer here that implies the same, there are scenarios where errors are too late for compile time but too early for runtime. Both answers? Dynamic SQL. (Some examples in Erland's article, too.) – Aaron Bertrand Feb 2 '17 at 2:27
10

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;
  • 1
    I like the temporary procedure - I would not have thought of that! – Max Vernon Feb 1 '17 at 16:37
1

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

  • I'm altering the table and inserting because this is part of a one-time data migration project. Obviously, I only showed a small snippet of relevant code. – Tom Baxter Feb 1 '17 at 17:11
  • You can do one after the other without having to do them in the same transactions. DDL creates its own implicit transactions (assuming you left the default Implicit Transactions setting on), but when you BEGIN a transaction you're bypassing the Implicit property of a DDL task until you COMMIT that transaction. – MguerraTorres Feb 1 '17 at 17:14
  • 1
    It's actually the fact that they're in the same batch, and nothing to do with transactions. – Aaron Bertrand Feb 1 '17 at 19:24
  • That's a good point. My mistake. – MguerraTorres Feb 1 '17 at 19:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.