CODEWITHSUNDEEP
sqlsql-servert-sqldata-migration
hwcverwe
hwcverwe

Reputation: 5367

Database Upgrade - Invalid column name

I am trying to migrate data during a database upgrade and I don't understand why scenario 1 is working but scenario 2 is throwing an exception:

Scenario 1

Query Scenario 1:

-- NewTable already created
IF EXISTS(select * from sys.tables where name = 'NewTable')
AND EXISTS(select * from sys.tables where name = 'ObsoleteTable')
BEGIN
    UPDATE newTable
    SET newTable.Name = obsoleteTable.Name
    FROM dbo.NewTable newTable
    INNER JOIN dbo.ObsoleteTable obsoleteTable
        ON obsoleteTable.Id = newTable.Id
END
-- ObsoleteTable will be removed after this step

Scenario 2

Query Scenario 2:

-- NewColumn has been created 
IF EXISTS(select * from sys.columns where object_id = object_id('MyTable') AND name = 'ObsoleteColumn')
AND EXISTS(select * from sys.columns where object_id = object_id('MyTable') AND name = 'NewColumn')
BEGIN
    UPDATE MyTable
    SET NewColumn = ObsoleteColumn
END
-- Obsolete Column will be removed after this step

Msg 207, Level 16, State 1, Line 4 Invalid column name 'ObsoleteColumn'.

Both scenarios are basically the same right? Only do the migration to the new structure if the obsolete Table/Column exists. Otherwise ignore.

If I execute scenario one while ObsoleteTable is already removed. it won't fail. So why does scenario 2 fail?

Upvotes: 0

Views: 773

Answers (1)

t-clausen.dk
t-clausen.dk

Reputation: 44336

It is a classic error, the column ObsoleteColumn does not exists. Which means the query will fail even when if the programflow never hits that row

This will also fail:

CREATE TABLE #xxx(a int)

IF 1 = 2
SELECT xyz FROM #xxx

To avoid it, you can use EXECUTE:

IF 1 = 2
  EXEC('UPDATE MyTable SET NewColumn = ObsoleteColumn')

Upvotes: 2

Related Questions