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

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.

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

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

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

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related