updating data after adding new column in a sigle T-SQL block

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 310
Note that the Emlpoyee table is having two columns such as EMPNAME, EMPID...
   
CREATE TABLE EMPLOYEE ( EMPID INT identity(1,1), EMPNAME varchar(100))
INSERT EMPLOYEE values( 'Chandu'),( 'Chandu2'),( 'Chandu3'),( 'Chandu4')


Now i wanted to add new column EMPLOYER and then add data to it

BEGIN 
Alter TABLE EMPLOYEE ADD EMPLOYER VARCHAR(50);
UPDATE EMPLOYEE SET EMPLOYER=EMPNAME
END


It gives the below error,
Msg 207, Level 16, State 1, Line 3
Invalid column name 'EMPLOYER'.


to resolve this error, we need to update tables data using Dynamic SQL query in the case of updating data after adding column in the SINGLE block

   BEGIN 
Alter TABLE EMPLOYEE ADD EMPLOYER VARCHAR(50);
EXEC ( 'UPDATE EMPLOYEE SET EMPLOYER=EMPNAME ')
END

Comments or Responses

Login to post response