Adding column and running an update query on the table to populate data in that column does not run in a single migration

[sumit-anantwar]

Describe the Bug We need to add two columns to a table and then populate the new columns with some data. Doing this in a single migration fails with error Invalid column name

Steps to Reproduce Steps to reproduce the behavior:

1. My migrations look like

   
   IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'pass' AND COLUMN_NAME = 'date_created')
   BEGIN
   ALTER TABLE [pass]
       ADD date_created datetime NOT NULL DEFAULT (GETDATE());
   END

IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'pass' AND COLUMN_NAME = 'date_updated') BEGIN ALTER TABLE [pass] ADD date_updated datetime NOT NULL DEFAULT (GETDATE()); END

UPDATE [pass] SET date_created = start_date, date_updated = start_date;

3. I ran migrate with the following options 
```go
driver, err := sqlserver.WithInstance(conn.DB, &sqlserver.Config{})
if err != nil {
    log.Fatal("Failed to create migration driver: ", err.Error())
}

migrationsPath := fmt.Sprintf("file:///db_migrations"

m, err := migrate.NewWithDatabaseInstance(
    migrationsPath,
    "sqlserver",
    driver,
)
if err != nil {
    log.Fatal("Failed to create migrate instance: ", err.Error())
}
err = m.Migrate(CurrentDBVersion)
if err != nil {
    log.Fatal("Failed to execute migration: ", err.Error())
}

5. See error

   
   Failed to execute migration: migration failed: Invalid column name 'date_updated'. in line 14: IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'pass' AND COLUMN_NAME = 'date_created')
   BEGIN
   ALTER TABLE [pass]
       ADD date_created datetime NOT NULL DEFAULT (GETDATE());
   END

IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'pass' AND COLUMN_NAME = 'date_updated') BEGIN ALTER TABLE [pass] ADD date_updated datetime NOT NULL DEFAULT (GETDATE()); END

UPDATE [pass] SET date_created = start_date, date_updated = start_date;

(details: mssql: Invalid column name 'date_updated'.)

**Expected Behavior**
The three queries should run in a sequence, and the update query should execute successfully.
Note that, these queries run without errors in Azure Data Studio.
Also, if we add the update query in a separate migration, it runs without errors.

**Migrate Version**
v4.17.1-0.20240102204802-0d4158977486

**Loaded Source Drivers**
sqlserver

**Loaded Database Drivers**
sqlserver

**Go Version**
go version go1.22.3 linux/amd64

**Stacktrace**
- NA - 

**Additional context**
- NA - 

[sumit-anantwar]

After some investigation, we found that the migrations are being executed inside an ExecContext https://github.com/golang-migrate/migrate/blob/2477f639fdba30c69a3b2dee3c788d4877cb2e97/database/sqlserver/sqlserver.go#L236

And it seems that the ALTER TABLE changes aren't applied until the ExecContext block exits. In which case, the new columns don't yet exist when the update is executed.

So, we tried to change the context for the update statement Putting the update statement inside an SQL EXEC block did the trick.

EXEC('UPDATE pass SET date_created = start_date, date_updated = start_date')

Iit would be great if someone could confirm if this is the correct approach.