alter添加列后,执行update时 (消息 207,级别 16,状态 1 列名无效的处理办法)


当我们对已存在的表进行增加列,并立即对表中的列进行 数据操作时,往往会发生找不到列的错误提示信息,
下文将讲述如何处理这种异常

例:

create table A(keyId int,info varchar(50))
 
insert into A (keyId,info) values(1,'a')
insert into A (keyId,info) values(2,'b')
insert into A (keyId,info) values(3,'C')
insert into A (keyId,info) values(4,'D')
insert into A (keyId,info) values(5,'e')
insert into A (keyId,info) values(6,'f')
insert into A (keyId,info) values(7,'g')
insert into A (keyId,info) values(8,'H')
insert into A (keyId,info) values(9,'i')

go

alter table A 
add writeDate datetime;
update A set writeDate=getdate() 

go
truncate table A
drop table A 
go



处理方案:
我们在sql脚本中,每段代码中都加入 “go” 分隔,将代码更好的分离

create table A(keyId int,info varchar(50))
 
insert into A (keyId,info) values(1,'a')
insert into A (keyId,info) values(2,'b')
insert into A (keyId,info) values(3,'C')
insert into A (keyId,info) values(4,'D')
insert into A (keyId,info) values(5,'e')
insert into A (keyId,info) values(6,'f')
insert into A (keyId,info) values(7,'g')
insert into A (keyId,info) values(8,'H')
insert into A (keyId,info) values(9,'i')

go

alter table A 
add writeDate datetime;
go

update A set writeDate=getdate() 

go
truncate table A
drop table A 
go