MSSQL循环添加列SQL code--这个表中放入ID,与我将要添加的列名select identity (int,1,1)as ID,HW_PART_NO
MSSQL循环添加列
- SQL code
--这个表中放入ID,与我将要添加的列名select identity (int,1,1)as ID ,HW_PART_NO into HW_Row from ['WINDOWS 7 32$'] group by HW_PART_NOdeclare @row nvarchar(255)declare @i intselect @i=COUNT(*)from SUB_Rowwhile(@i>0)beginselect @row=SUB_PH_NUMBER_AND_DESCRIPTION from SUB_Row where SUBID=@iset @i=@i-1alter table SRV_SUB add @row nvarchar(255)end报错:Msg 102, Level 15, State 1, Line 8Incorrect syntax near '@row'.
语句我个人认为是没有问题的,因为我昨天也同样适用这个语句添加了新列.
估计是不是数据类型有问题?或者说是声明的变量使用有问题
[解决办法]
- SQL code
--这个表中放入ID,与我将要添加的列名select identity (int,1,1)as ID ,HW_PART_NO into HW_Row from ['WINDOWS 7 32$'] group by HW_PART_NOdeclare @row nvarchar(255)declare @i intselect @i=COUNT(*)from SUB_Rowwhile(@i>0)beginselect @row=SUB_PH_NUMBER_AND_DESCRIPTION from SUB_Row where SUBID=@iset @i=@i-1EXEC ('alter table SRV_SUB add '+@ROW+' nvarchar(255)')end
[解决办法]
- SQL code
select identity (int,1,1)as ID ,HW_PART_NO into HW_Row from ['WINDOWS 7 32$'] group by HW_PART_NOdeclare @row nvarchar(255)declare @i intselect @i=COUNT(*)from SUB_Rowwhile(@i>0)beginselect @row=SUB_PH_NUMBER_AND_DESCRIPTION from SUB_Row where SUBID=@iset @i=@i-1exec('alter table SRV_SUB add '+@row+' nvarchar(255)')end
[解决办法]
try this,
- SQL code
alter table SRV_SUB add @row nvarchar(255)--> 改为exec('alter table SRV_SUB add ['+@row+'] nvarchar(255) ')
[解决办法]
- SQL code
EXEC ('alter table SRV_SUB add '+@ROW+' nvarchar(255)')
[解决办法]
DDL语句不支持带变量.