CODEWITHSUNDEEP
sqlsql-serversql-server-2014ddl
Craig
Craig

Reputation: 18704

Table alter script failing because it can't see what it's trying to add

I need to execute a script that adds a new column to a table, and then updated that column based on an existing column.

PRINT 'Adding Column to Table...'

ALTER TABLE dbo.Table
ADD [Column] DATETIME2 NULL;

PRINT 'Updating data...'
UPDATE dbo.Table
SET [column] = ISNULL([ModifiedDate],[CreatedDate]) 

PRINT 'Finalising table structure...'
ALTER TABLE dbo.Table
ALTER COLUMN [Column] DATETIME2 NOT NULL

PRINT 'Complete!'

However, when I execute it in one go, it errors on the Update, saying that 'Column' doesn't exist.

If I run it one line at a time, it works.

Is there a way to get around this?

I can bypass this by using an EXEC to do the UPDATE, but that seems hacky. is it the only option?

Upvotes: 0

Views: 38

Answers (2)

Shushil Bohara
Shushil Bohara

Reputation: 5656

I also think so as @Gordon said, you can get rid of the issue by using GO between the statements, as below:

PRINT 'Adding Column to Table...'

ALTER TABLE dbo.[Table] ADD [Column] DATETIME2 NULL;

GO

PRINT 'Updating data...'
UPDATE dbo.[Table]
SET [column] = ISNULL([ModifiedDate],[CreatedDate]) 

GO

PRINT 'Finalising table structure...'
ALTER TABLE dbo.[Table]
ALTER COLUMN [Column] DATETIME2 NOT NULL

PRINT 'Complete!'

Can you please try with this code. I have enclosed table name in square brackets, it's a reserved keyword so unable to execute in SQL2012 and it was working to my end without GO.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270021

The problem is that when the UPDATE is compiled, the column is not know. I think you can fix that problem by inserting GO between the different commands.

But, why not just use a computed column:

ALTER TABLE dbo.Table ADD [Column] AS COALESCE([ModifiedDate],[CreatedDate]) ;

With a computed column, you don't need to do an update, and the value is always correct.

Upvotes: 2

Related Questions