Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update Column after Alter Table ADD column Expand / Collapse
Author
Message
Posted Thursday, May 2, 2013 12:05 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 7, 2013 9:20 PM
Points: 13, Visits: 80
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.
Post #1448698
Posted Thursday, May 2, 2013 7:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:36 PM
Points: 13,418, Visits: 12,283
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1448818
Posted Thursday, May 2, 2013 9:13 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 1:49 AM
Points: 1,694, Visits: 19,551
Depending on your requirements, you can do this in one statement

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



____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1448852
Posted Thursday, May 2, 2013 10:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 14, 2013 4:58 AM
Points: 1, Visits: 24
oh.. i thought its got something to do with the difference of usage of DDL and DML statements in a transaction...
thanks a ton..
Post #1448885
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse