Transaction within IF THEN ELSE doesn't commit
- by boris callens
In my TSQL script I have an IF THEN ELSE structure that checks if a column already exists.
If not it creates the column and updates it.
IF NOT EXISTS(
SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tableName' AND COLUMN_NAME = 'columnName'))
BEGIN
BEGIN TRANSACTION
ALTER TABLE tableName
ADD columnName int NULL
COMMIT
BEGIN TRANSACTION
update tableName
set columnName = [something]
from
[subquery]
COMMIT
END
This doesn't work because the column doesn't exist after the commit.
Why doesn't the COMMIT commit?
