现在A表(不超过100列)
GH XM
1 a
2 b
3 c
4 d
. .
. .
要在B表中添加列,列名为A表的GH+XM(例1a,2b),A表有多少条记录就在B表中添加多少个列
我写的怎么有问题?
declare @a int,@b int
select @a=count(*) from a
set @b=0
while @b>=@a
begin
@b=@b+1
atti tabel a
add @b int
conitue
end
GH XM
1 a
2 b
3 c
4 d
. .
. .
要在B表中添加列,列名为A表的GH+XM(例1a,2b),A表有多少条记录就在B表中添加多少个列
我写的怎么有问题?
declare @a int,@b int
select @a=count(*) from a
set @b=0
while @b>=@a
begin
@b=@b+1
atti tabel a
add @b int
conitue
end
select @a=(select count(*) from a)
set @b=0
while @b>=@a
begin
@b=@b+1
atti tabel a
add @b int
conitue
end
add @b int
这一行有问题
先测试一下
insert a select 1, 'a' union all
select 2, 'b' union all
select 3, 'c' union all
select 4, 'd'
create table b(id int)
alter table b add 新增列 varchar(3)nulldeclare @a int,@b int
select @a=(select count(*) from a)
set @b=1
while @b!>@a
begin
insert b(新增列) VALUES(@b)
select @b=@b+1
endselect * from b
楼主这样试试
set @a=0
set @cwl=(select count(*) from zhanduan_info)
while @a>=@cwl
begin
set @a=@a+1
alter table [ado].[bb_info]
add @a int
conitue
end这是修改了的原码,老是说在ADD @a int这行出现语法错误
select @a=(select count(*) from a)
set @b=0
while @b>=@a
begin
set @b=@b+1
exec ('alter table a add '+ @b + 'int')
continue
end
select @a=4
set @b=0
while @b!>@a
begin
update b
set id=@b,@b=@b+1
end
如果B表有值就这样
INSERT INTO A
SELECT 1, 'a'
UNION ALL SELECT 2, 'b'
UNION ALL SELECT 3, 'c'
UNION ALL SELECT 4, 'd'CREATE TABLE B(A INT)DECLARE CUR CURSOR FOR SELECT CAST(GH AS VARCHAR) + XM FROM A FOR READ ONLY
DECLARE @C VARCHAR(10)
OPEN CUR
FETCH FROM CUR INTO @C
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('ALTER TABLE B ADD [' + @C + '] VARCHAR(10)')
FETCH FROM CUR INTO @C
END
CLOSE CUR
DEALLOCATE CURSELECT * FROM BDROP TABLE A,B
exec ('alter table a add '+ @b + 'int')谢谢大家了
set @a=0
set @cwl=(select count(*) from zhanduan_info)
while @a<=@cwl
begin
set @a=@a+1
exec ('alter table a add '+ 'b'+@a + 'int')
conitue
end
这是修正过的全部代码,谢谢大家了