How to update a table if a column exists in SQL Server?

Question

I have a table MyTable created by

CREATE TABLE MyTable
(
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [Type] [int] NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
)

I want to check if a column exists in my table, and if it does, I want to copy the data to a different column, then drop the old column, like this:

IF (SELECT COLUMNPROPERTY(OBJECT_ID('MyTable'), 'Timestamp', 'Precision')) IS NOT NULL
BEGIN
    UPDATE [dbo].[MyTable]
    SET [CreatedDate] = [Timestamp]

    ALTER TABLE [dbo].[MyTable]
    DROP COLUMN [Timestamp]
END
GO

However, when I try to run this I get an error:

Invalid column name 'Timestamp'

How can I accomplish what I'm trying to do?

Answer 1

This is a compilation issue.

If the table doesn't exist when you compile the batch all works fine as the statements referencing the table are subject to deferred compile. However for a preexisting table you will hit this problem as it tries to compile all statements and balks at the non existent column.

You can push the code into a child batch so it is only compiled if that branch is hit.

IF (SELECT COLUMNPROPERTY(OBJECT_ID('MyTable'), 'Timestamp', 'Precision')) IS NOT NULL
BEGIN
EXEC('
    UPDATE [dbo].[MyTable]
    SET [CreatedDate] = [Timestamp]

    ALTER TABLE [dbo].[MyTable]
    DROP COLUMN [Timestamp]
    ')
END
GO

If you are just trying to rename the column

EXEC sys.sp_rename 'dbo.MyTable.[TimeStamp]' , 'CreatedDate', 'COLUMN'

Would be easier though (from a position where the CreatedDate column doesn't exist).