Adding a column to a table then updating the table



  • I have a code that looks something like this

    declare @query = '
       Alter Table Temp
       Add NewColumn int
    

    update Temp
    set NewColumn = 100'

    exec (@query)

    It will give error NewColumn does not exist. But if I run this normally without using @query = '' it runs fine. Also, what is it called when you run the code like this with the @query = ' text ' then exec?



  • You are executing them as one batch in dynamic SQL. So the compiler will not allow it, because at compilation time the column does not exist yet.

    It is not true that it would work when executed together. you are executing them line-by-line in separate batches.

    • This works: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=4699a2a366cd1eb4f18bf7f22fc4604b
       Alter Table Temp
       Add NewColumn int
    
       update Temp
       set NewColumn = 100
    
    • This does not: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=836fcc362015baa0c57244f2a5e4a2f9
       Alter Table Temp
       Add NewColumn int
    

    update Temp
    set NewColumn = 100

    So you need to do this

    declare @query nvarchar(max) = '
       Alter Table Temp
       Add NewColumn int
    ';
    

    exec sp_executesql @query;

    set @query = '
    update Temp
    set NewColumn = 100
    ';

    exec sp_executesql @query;

    https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=4699a2a366cd1eb4f18bf7f22fc4604b

    The ALTER could also go outside the dynamic SQL as well.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2