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?

so what does the select columnproperty(...) sub-query return? I'm guessing it isn't null, so your update runs, and since timestamp really DOESN'T exist, you get that error. — Marc B
– Marc B, Commented Jul 18, 2016 at 18:48
why mysql tag .. your code seems sqlserver .. remove the improper tags please — ScaisEdge
– ScaisEdge, Commented Jul 18, 2016 at 18:51
sql statements in a sproc are evaluated for correctness when the sproc itself is compiled. That means your if() doesn't really apply when the sproc is parsed and your table is checke for a Timestamp field AT TIME OF COMPILE. You'd have to do it as dynamic sql. e.g. sql = 'UPDATE ..'; exec @sql-type thing — Marc B
– Marc B, Commented Jul 18, 2016 at 18:59

Martin Smith · Accepted Answer · 2016-07-18 19:25:04Z

15

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).

edited Jul 18, 2016 at 19:25

answered Jul 18, 2016 at 18:59

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

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

Comments

Tab Alleman · Accepted Answer · 2016-07-18 19:01:19Z

0

You have to first create the [Timestamp] column with an ALTER TABLE statement.

Then the rest should run.

EDIT based on comment (I know this info is duplicated elsewhere on SO, but I couldn't find it):

Ok, the IF condition in SQL Server unfortunately does not allow you to ignore code that does not parse. What is happening is that SQL Server is looking at your command, and parsing every statement to make sure it is valid.

When it does this, SQL Server isn't smart enough to figure out that the invalid statement (the UPDATE that requires the presence of [TimeStamp]) will not be reached if there is no [TimeStamp].

In other words, you can't write a SQL command that expects a column that doesn't exist EVEN IF you nest that command in an IF condition that won't be reached. SQL Server will parse the entire statement and not allow it to run BEFORE it tests the IF condition.

A commonly used Work arounds for this is Dynamic SQL, which SQL Server can't pre-parse, so it won't try.

edited Jul 18, 2016 at 19:01

answered Jul 18, 2016 at 18:48

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

1 Comment

Sean Smyth Over a year ago

The point is to check if my system has already been altered to delete this column. Readding it is not the point. IF it still exists, that means my script hasn't been run before. If the column doesn't exist, I want it to skip this entire block of code

Collectives™ on Stack Overflow

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

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