How to update an added column in the same command statement

Question

In SQL Server 2008, I would like to add a column to the table and update it immediately after, but only if they have not been created before. I do not want to start the update if a column was previously created.

IF NOT EXISTS (SELECT *
               FROM [INFORMATION_SCHEMA].[COLUMNS] 
            WHERE [TABLE_NAME] = 'SETTINGS' AND [COLUMN_NAME] = 'COLOR_SCHEME')
BEGIN
    ALTER TABLE [SETTINGS]
    ADD [COLOR_SCHEME] int NULL

    UPDATE [SETTINGS]
    SET [COLOR_SCHEME] = 1
END

The "GO" room after adding a column does not work, because it will not be a complete batch statement, but if I try to run it like this, I get the error "Invalid column name COLOR_SCHEME". "

Any ideas on how to make the column exist when the update starts?

Answer 1

You can try using dynamic SQL instead of the alter statement:

DECLARE @SQL NVARCHAR(4000)
SET @SQL='ALTER TABLE [SETTINGS] ADD [COLOR_SCHEME] int NULL'
EXEC(@SQL)

Answer 2

, IF :

CREATE TABLE ##Temp_Add_Color_Scheme (new_column BIT)
INSERT INTO ##Temp_Add_Color_Scheme VALUES (0)

IF NOT EXISTS (SELECT *
               FROM [INFORMATION_SCHEMA].[COLUMNS] 
               WHERE [TABLE_NAME] = 'SETTINGS' AND
                     [COLUMN_NAME] = 'COLOR_SCHEME')
BEGIN
    UPDATE ##Temp_Add_Color_Scheme SET new_column = 1

    ALTER TABLE [SETTINGS]
    ADD [COLOR_SCHEME] int NULL
END
GO

DECLARE @new_column BIT
SELECT @new_column = new_column FROM ##Temp_Add_Color_Scheme

IF (@new_column = 1)
BEGIN
    UPDATE [SETTINGS]
    SET [COLOR_SCHEME] = 1
END

DROP TABLE ##Temp_Add_Color_Scheme

Answer 3

If the contents of the column are fixed, can you just put the default value in it instead of updating it?

ALTER TABLE [SETTINGS] 
    ADD [COLOR_SCHEME] int NULL 
    DEFAULT 1 WITH VALUES ;