Update Column after Alter Table ADD column

Question

Update Column after Alter Table ADD column

_rohit_

Say Hey Kid

Points: 701
More actions
May 2, 2013 at 12:05 am

#272020

hi,
I just came across an error, the cause of which is unknown to me.
why would a TSQL :
-----------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'Schema1' AND TABLE_NAME = 'Table1' AND COLUMN_NAME = 'Column1')
BEGIN
ALTER TABLE Schema1.Table1 ADD Column1 int NULL
UPDATE Schema1.Table1
SET Column1 = -1
ALTER TABLE Schema1.Table1 ALTER COLUMN Column1 int NOT NULL
END
------------------------------------------------
throw an error:
Msg 207, Level 16, State 1, Line 5
Invalid column name 'Column1'.
while running the update command.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286602 More actions · Answer 1

rohit.anshu (5/2/2013)
hi,
I just came across an error, the cause of which is unknown to me.
why would a TSQL :
-----------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'Schema1' AND TABLE_NAME = 'Table1' AND COLUMN_NAME = 'Column1')
BEGIN
ALTER TABLE Schema1.Table1 ADD Column1 int NULL
UPDATE Schema1.Table1
SET Column1 = -1
ALTER TABLE Schema1.Table1 ALTER COLUMN Column1 int NOT NULL
END
------------------------------------------------
throw an error:
Msg 207, Level 16, State 1, Line 5
Invalid column name 'Column1'.
while running the update command.

Because they are in the same batch and it won't compile because the column doesn't exist. Put a GO in between.

ALTER TABLE Schema1.Table1 ADD Column1 int NULL

GO

UPDATE Schema1.Table1

SET Column1 = -1

GO

ALTER TABLE Schema1.Table1 ALTER COLUMN Column1 int NOT NULL

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Mark Cowne One Orange Chip Points: 26981 More actions · Answer 2

Depending on your requirements, you can do this in one statement

ALTER TABLE Schema1.Table1 ADD Column1 int NOT NULL DEFAULT(-1)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

rajat.bet Valued Member Points: 69 More actions · Answer 3

oh.. i thought its got something to do with the difference of usage of DDL and DML statements in a transaction...

thanks a ton..

Update Column after Alter Table ADD column

Cookies on SQLServerCentral