- I was troubleshooting an issue with a SQL Server script that we use to upgrade the database of an application we develop. I ran into the weird situation where if I renamed a column in a table and then tried to update that in the script I would get a script compile time error saying that the column didn’t exist. If I created a temp table with one row in it and did a cross join in the update statement like below the script would compile and run just fine. Most of the websites I looked at explained that you could also accomplish the same thing by using dynamic sql with the update statement. Somehow the compilation step for this “hack” I did with the temp table must be forcing the sql statement to be compiled in a more dynamic sense.
select ‘1’ as SomeText
update q set q.ValueTXT = convert(varchar(255), q.ValueTXTTMP) from [dbo].[SomeImportantTable] q cross join #dummytable p