Alter Table Add Column and update the new Column i

问题:

I'm trying to add column and update it in the same if statement:

BEGIN TRAN

IF NOT EXISTS(SELECT 1 FROM sys.columns 
              WHERE Name = N'Code' 
              AND Object_ID = Object_ID(N'TestTable'))
BEGIN
    ALTER TABLE TestTable 
    ADD Code NVARCHAR(10)

    UPDATE TestTable 
    SET Code = Name 
    WHERE 1=1
END

COMMIT

It throws an error:

Invalid column name 'Code'

Is there any ways how to do these operations in one transaction?

回答1:

You can use the EXEC statement to accomplish this.

BEGIN TRAN

IF NOT EXISTS(SELECT 1 from sys.columns where Name = N'Code' and Object_ID = Object_ID(N'TestTable'))
BEGIN
    ALTER TABLE TestTable ADD Code NVARCHAR(10)

    Exec ('UPDATE TestTable SET Code = Name WHERE 1=1')
END

COMMIT

This is the test code I used.

Create Table TestTable(Id Int, Name VarChar(20))

Insert Into TestTable Values(1,'George')

BEGIN TRAN

IF NOT EXISTS(SELECT 1 from sys.columns where Name = N'Code' and Object_ID = Object_ID(N'TestTable'))
BEGIN
    ALTER TABLE TestTable ADD Code NVARCHAR(10)

    Exec ('UPDATE TestTable SET Code = Name WHERE 1=1')
END

COMMIT

Select * From TestTable

Drop Table TestTable

回答2:

You are running into the issue whereby the entire statement is parsed, and the DML fails because the Code column doesn't exist yet. You now have the conflict:

• ALTER TABLE requires a GO (batch execution)
• Your multi line batch logic requires a BEGIN / END wrapper

You'll need to find another way to retain the state of 'Add Code' logic across multiple statement batches, e.g. use a #temp table:

CREATE TABLE #tmpFlag(AddCode BIT);

IF NOT EXISTS(SELECT 1 from sys.columns where Name = N'Code' and Object_ID = Object_ID(N'TestTable'))
BEGIN
    INSERT INTO #tmpFlag VALUES(1);
    ALTER TABLE TestTable ADD Code NVARCHAR(10);
END;
GO

IF EXISTS (SELECT * FROM #tmpFlag)
BEGIN
   UPDATE TestTable SET Code = Name;
END;

DROP TABLE #tmpFlag;

SqlFiddle here

回答3:

Try this, see the live example in SQL Server 2008

BEGIN TRAN

    IF NOT EXISTS(SELECT 1 from sys.columns where Name = N'Code' and Object_ID = Object_ID(N'TestTable'))
    BEGIN
        ALTER TABLE TestTable ADD Code NVARCHAR(10)
        Go
        SET XACT_ABORT ON
        Go
        UPDATE TestTable SET Code = Name WHERE 1=1
    END

    COMMIT

回答4:

You dont need begin tran since both ALTER & UPDATE are TRANSACTION.

IF NOT EXISTS(SELECT 1 FROM sys.columns 
              WHERE Name = N'Code' 
              AND Object_ID = Object_ID(N'TestTable'))
BEGIN
BEGIN
    ALTER TABLE TestTable 
    ADD Code NVARCHAR(10)
END
GO
BEGIN
    UPDATE TestTable 
    SET Code = Name 
    WHERE 1=1
END
GO
END