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