Add column if it does not exist

Question

I am running SSIS for data warehousing purposes basically it loops through all my databases and saves that data into a table. However, some tables are missing a [LastModified] column and I would like to create the column for those that do not match.

This is my select statement -

IF EXISTS(SELECT 1 FROM sys.columns 
      WHERE Name = N'LastModified'
      AND Object_ID = Object_ID(N'dbo.Branch'))
(SELECT [ID]
,ISNULL([Name], '') AS [Name]
,ISNULL([Code], '') AS [Code]
,ISNULL([LastModified], '') AS [LastModified]
,[TimeStampField] from [dbo].[Branch])
ELSE
(SELECT [ID]
,ISNULL([Name], '') AS [Name]
,ISNULL([Code], '') AS [Code]
,getdate() AS [LastModified]
,[TimeStampField] from [dbo].[Branch])

I get the error - Invalid column name 'LastModified'.

If I change ISNULL([LastModified], '') AS [LastModified] to GetDate() AS [LastModified] it works. However, this will change the date on those tables which have the [LastModified] column.

Is there a better way to implement this, the Derived Column function maybe?

score 3 · Accepted Answer · answered Oct 08 '18 at 13:03

You can do this with dynamic sql:

DECLARE @sql varchar(max);

IF EXISTS(SELECT 1 FROM sys.columns 
      WHERE Name = N'LastModified'
      AND Object_ID = Object_ID(N'dbo.Branch'))
SET @sql = 'SELECT [ID]
,ISNULL([Name], '') AS [Name]
,ISNULL([Code], '') AS [Code]
,ISNULL([LastModified], '') AS [LastModified]
,[TimeStampField] from [dbo].[Branch]';
ELSE
SET @sql = 'SELECT [ID]
,ISNULL([Name], '') AS [Name]
,ISNULL([Code], '') AS [Code]
,getdate() AS [LastModified]
,[TimeStampField] from [dbo].[Branch]';

EXEC(@sql);

Using Dynamic SQL hides from the parser the fact that one of the possible paths in your code references a column that doesn't exist.

Add column if it does not exist

1 Answers1