Add column to existing table and default value to another column without dynamic sql

Question

For Sql Server 2005 and 2008 I want to check if a column already exists on a given table and create it if it doesn't. This new column should have a default value of an ExistingColumn. Currently I need to use dynamic sql to fill the new column because sql server will complain of a syntax error.

Here is the current sql server code:

IF NOT EXISTS (SELECT TOP 1 1 FROM sys.columns WHERE [name] = N'NewColumn' AND OBJECT_ID = OBJECT_ID(N'ExistingTable'))
BEGIN
    ALTER TABLE [dbo].[ExistingTable] ADD [NewColumn] VARCHAR(50) NULL;

    exec sp_executesql  N'UPDATE [dbo].[ExistingTable] SET NewColumn = ExistingColumn'

    ALTER TABLE [dbo].[ExistingTable] ALTER COLUMN  [NewColumn] VARCHAR(50) NOT NULL 
END
GO

Is there any other way to solve this problem without resorting to dynamic sql?

I don't have a problem with it aside from not getting the syntax highlighting and auto complete of Management Studio. More curious as to whats really going on under the covers. — Joshua Jewell
– Joshua Jewell, Commented Mar 7, 2013 at 19:04
Ah, I usually replace the exec with Print, then paste that in another window it's not brill but it's better that trying to figure out what error near , means — Tony Hopkinson
– Tony Hopkinson, Commented Mar 7, 2013 at 20:44

Aaron Bertrand · Accepted Answer · 2013-03-07 19:12:42Z

2

SQL Server is parsing your statement before your ALTER runs, and saying "Hey, no such column." The parser doesn't understand IF and other branching and can't follow the sequence of events when you mix DDL and DML - or predict the sequence the events will take and what branching will happen at runtime.

Deferred name resolution allows you to access objects that don't exist yet, but not columns that don't exist yet on objects that do.

So, dynamic SQL seems like the way you'll have to do it.

answered Mar 7, 2013 at 19:12

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jason Whitish · Accepted Answer · 2013-03-07 22:18:23Z

1

Since you're creating the column regardless, you could do two separate batches.

    IF NOT EXISTS (SELECT TOP 1 1 FROM sys.columns WHERE [name] = N'NewColumn' AND OBJECT_ID = OBJECT_ID(N'ExistingTable'))
    BEGIN
        ALTER TABLE [dbo].[ExistingTable] ADD [NewColumn] VARCHAR(50) NULL;
    END
    GO
    IF EXISTS (SELECT TOP 1 1 FROM sys.columns WHERE [name] = N'NewColumn' AND OBJECT_ID = OBJECT_ID(N'ExistingTable'))
    BEGIN
        IF EXISTS (SELECT 1 FROM [dbo].[ExistingTable] WHERE NewColumn IS NULL)
        BEGIN
            UPDATE [dbo].[ExistingTable] SET NewColumn = ExistingColumn
            ALTER TABLE [dbo].[ExistingTable] ALTER COLUMN  [NewColumn] VARCHAR(50) NOT NULL 
        END
    END
    GO

answered Mar 7, 2013 at 22:18

Jason Whitish

1,4381 gold badge23 silver badges27 bronze badges

1 Comment

Jason Whitish Over a year ago

It's worth noting that you'll still get the error if you run the second batch on its own without having ran the first batch or if the first batch errors; all the above does is get you out of doing dynamic SQL. So if you're looking for repeatability, then you're stuck.

Collectives™ on Stack Overflow

Add column to existing table and default value to another column without dynamic sql

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related