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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy