June 24, 2011 at 11:14 am
I am basically testing if a column is there and if it is i am printing a statement, Rt now i am just slapping a print if it already exits but if the column already exists i want to add the column name,and data in the column derived from the select that checks the existence of the column, is there anyway i do that on a print statement ??
If exists
(SELECT table_name, Column_name, Data_TYPE FROM INFORMATION_SCHEMA.COLUMNS
where column_name = 'X' and table_name = 'Y')
Print 'The column X already exits'
ELSE
Begin
Alter table Y Add X bit NULL
UPDATE X SET Y = 'false'
END
June 24, 2011 at 11:47 am
sure you can, but you have to tackle the test just a little differently;
also,you'll have to switch to dynamic SQL to do the ALTEr + UPDATE part.
how about this?:
DECLARE @tableName VARCHAR(255),
@columnName VARCHAR(255),
@datatype VARCHAR(255)
/*
SELECT OBJECT_NAME(OBJECT_ID),
name,
TYPE_NAME(system_type_id) FROM sys.columns
WHERE OBJECT_NAME(OBJECT_ID) = 'TBLEXAMPLE'
AND name = 'COURSE'
*/
SELECT
@tableName = OBJECT_NAME(OBJECT_ID),
@columnName = name ,
@datatype=TYPE_NAME(system_type_id)
FROM sys.columns
WHERE OBJECT_NAME(OBJECT_ID) = 'TBLEXAMPLE'
AND name = 'COURSE'
IF @tableName IS NOT NULL
PRINT 'The column ' + @columnName + ' with datatype ' + @datatype + 'already exists in table ' + @tableName
ELSE
BEGIN
PRINT 'Column does not exist! Starting Work!'
EXEC('ALTER TABLE TBLEXAMPLE ADD COURSE BIT NULL ; UPDATE TBLEXAMPLE SET COURSE = 0;')
END
Lowell
June 24, 2011 at 12:19 pm
Thanks that works and is much more effective than my approach
thanks again
June 27, 2011 at 6:12 am
Hi Lowell,
EXEC('ALTER TABLE MyTable ADD MyValue1 Int NULL; UPDATE MyTable SET MyValue1 = 0; ')
Update statement doesn't recognize the newly added column and returns the error as "Invalid column name"
Pls advise solution?
June 27, 2011 at 6:28 am
doh yeah i see how that could happen;
make it two distinct dynamic SQLS:
ELSE
BEGIN
PRINT 'Column does not exist! Starting Work!'
EXEC('ALTER TABLE TBLEXAMPLE ADD COURSE BIT NULL ;' )
EXEC('UPDATE TBLEXAMPLE SET COURSE = 0;')
END
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply