May 2, 2013 at 12:05 am
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.
May 2, 2013 at 7:48 am
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/
May 2, 2013 at 9:13 am
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/61537May 2, 2013 at 10:01 am
oh.. i thought its got something to do with the difference of usage of DDL and DML statements in a transaction...
thanks a ton..
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply