Question

SQL语句是否错了，提示列名无效，

SELECT itm=IDENTITY(INT,1,1),*
into #zztable_name
from
(select b.name from syscolumns a,sysobjects b where a.id=b.id and a.name='PRD_NO') a
declare @table_name varchar(30)
declare @prd_no varchar(30)
declare @xprd_no varchar(30)
declare @m varchar(200)
declare @j int
declare @i int
set @i=1
set @j=1
while @j<300
begin
select @table_name=name from #zztable_name where itm=@j
while @i<4
begin
select @prd_no=prd_no from #zzprd_th where itm=@i
select @xprd_no=xprd_no from #zzprd_th where itm=@i
set @m='update db_yszt.dbo.'+@table_name+' set prd_no='+@xprd_no+' where prd_no='+@prd_no
exec(@m)
set @i=@i+1
end
set @j=@j+1
end

create table #zzprd_th(itm int,prd_no varchar(20),xprd_no varchar(20))
insert into #zzprd_th values(1,'ZZ99-LS-008','ZZ99-LS-00800')
insert into #zzprd_th values(2,'ZZ99-LS-005','ZZ99-LS-00500')
insert into #zzprd_th values(3,'ZZ99-LS-004','ZZ99-LS-00400')

Answer 1

SELECT itm=IDENTITY(INT,1,1)

这句错了吧
SELECT @itm=IDENTITY(INT,1,1) 可以这样

SELECT IDENTITY(INT,1,1) as itm 也开这样
看你想要的应该是后面的一种，生成一个自增序号列

Answer 2

set @m='update db_yszt.dbo.'+@table_name+' set prd_no='+@xprd_no+' where prd_no='+@prd_no
这句要改一下：

set @m='update db_yszt.dbo.'+@table_name+' set prd_no=‘’'+@xprd_no+'‘’ where prd_no='‘’+@prd_no + ''' '