begin tran tran_update
update tcunit set ivalid=0 where cndesc not in (select unitname from tcdhunit where valid = 1)
update tcunit set ivalid=0 where endesc=''
update tcunit set ivalid=1 where endesc='lots'
alter table tcunit drop CONSTRAINT PK_tcunit
alter table tcunit drop column code
alter table tcunit add [codes] [int] IDENTITY(1,1) not null
alter table tcunit add [code] [varchar](4) null
INSERT INTO tcunit (CNDesc,endesc,ivalid,code) select unitname,unitname,valid ,'' from tcdhunit where valid=1 and unitname not in (select endesc from tcunit where ivalid=1)
begin tran tran_column with
update tcunit set code = '00'+Convert(varchar(4),[codes])
commit tran tran_column
update tcunit set code = '0'+code where len(code)<4
alter table tcunit drop column [codes]
alter table tcunit alter column [code] [varchar](4) not null
alter table tcunit add constraint [PK_tcunit] PRIMARY KEY CLUSTERED ([code])
commit tran tran_update
这个是一段将数据库的一个表中的数据导入另外一个表,总是报“列名 ’codes‘ 无效”的一个错误,我不知道是怎么回事。。用GO的话就没问题,问题是我这个语句是要在C#程序里来运行的,加GO在C#程序里会抛异常。
begin tran tran_update
update tcunit set ivalid=0 where cndesc not in (select unitname from tcdhunit where valid = 1)
update tcunit set ivalid=0 where endesc=''
update tcunit set ivalid=1 where endesc='lots'
go
alter table tcunit drop CONSTRAINT PK_tcunit
go
alter table tcunit drop column code
go
alter table tcunit add [codes] [int] IDENTITY(1,1) not null
go
alter table tcunit add [code] [varchar](4) null
go
INSERT INTO tcunit (CNDesc,endesc,ivalid,code) select unitname,unitname,valid ,'' from tcdhunit where valid=1 and unitname not in (select endesc from tcunit where ivalid=1)
begin tran tran_column with
update tcunit set code = '00'+Convert(varchar(4),[codes])
commit tran tran_column
update tcunit set code = '0'+code where len(code)<4
go
alter table tcunit drop column [codes]
go
alter table tcunit alter column [code] [varchar](4) not null
go
alter table tcunit add constraint [PK_tcunit] PRIMARY KEY CLUSTERED ([code])
go
commit tran tran_update
这里增加一列,后面又马上插入数据是检测不到的,加GO不行吗?加GO在C#程序里会抛异常。
你写一个过程放过程里呢
的
go 已经将其他的语句分成不同的批处理了
code cn en valid
0001 cn1 en1 1
0002 cn2 en2 1
表2
code unit valid
000000000000001 unit1 1
000000000000002 unit2 1
现在是要把表2的记录添加到表1,表2的unit赋值给表1的cn和en字段,表1的code字段是主键,最后的结果要是这样
表1
code cn en valid
0001 cn1 en1 1
0002 cn2 en2 1
0003 unit1 unit1 1
0004 unit2 unit2 1