请问如何向一个表添加大量字段？100分送上。。

[chenjinhuang1978]

有一个表，表名为message,现需要向该表添加a1,a2,a3,a4.....到a320，同时添加a1_reader,a2_reader，.....到a320_reader,请问如何操作啊？不会手动吧。哈哈。。。

[yujohny]

CREATE TABLE #aa (ID INT IDENTITY(1,1),CC INT)

DECLARE @str nvarchar(200),@colstr NVARCHAR(10),@I INT

SET @I =1

WHILE @I <= 320
BEGIN
SET @colstr ='a'+ CONVERT(NVARCHAR(5),@I)

SET @str ='ALTER TABLE #aa ADD ' + @colstr +' INT'

EXEC(@str)
SET @I = @I + 1
END

SET @I = 1

WHILE @I <= 320
BEGIN
SET @colstr ='a'+ CONVERT(NVARCHAR(5),@I)+'_reader'

SET @str ='ALTER TABLE #aa ADD ' + @colstr +' INT'

EXEC(@str)
SET @I = @I + 1
END


SELECT * FROM #aa

DROP TABLE #aa

[txlicenhe]

--create table message(id int)

declare @i int
set @i = 1
while @i <= 320
begin
exec('alter table message add a'+@i+' int ')
exec('alter table message add a'+@i+'_reader varchar(10)')
set @i = @i+1
end

--drop table message

[txlicenhe]

--create table message(id int)

declare @i int
set @i = 1
while @i <= 320
begin
exec('alter table message add a'+@i+' int ')
exec('alter table message add a'+@i+'_reader varchar(10)')
set @i = @i+1
end

--drop table message

[hjb111]

create procedure p_addcolname
as
declare @n as int
declare @s as varchar(8000)
set @n=1
set @s=''
while @n>320
begin
set @s=@s+'a'+cast(@n as varchar(3))+','
set @n=@n+1
end
set @n=1
while @n>319
begin
set @s=@s+'a'+cast(@n as varchar(3))+'_reader,'
set @n=@n+1
end
set @s=@s+'a320_reader'
exec('alter table yourtable add columns '+@s)

go

[愉快的登山者]

若数据a1_reader,a2_reader，.....到a320_reader在一个表中，如：reader_table

insert message
select * from reader_table

[yujohny]

如果你的表字段可以这样排列的话， a1,a1_reader,a2,a2_reader……
那就可以简化为：
CREATE TABLE message (ID INT IDENTITY(1,1))

DECLARE @str nvarchar(200),@colstr NVARCHAR(10),@I INT

SET @I =1

WHILE @I <= 320
BEGIN
SET @colstr ='a'+ CONVERT(NVARCHAR(5),@I)

SET @str ='ALTER TABLE message ADD ' + @colstr +' INT'

EXEC(@str)

SET @colstr ='a'+ CONVERT(NVARCHAR(5),@I) + '_reader'

SET @str ='ALTER TABLE message ADD ' + @colstr +' INT'

EXEC(@str)

SET @I = @I + 1
END

SELECT * FROM message

[sdhdy]

declare @i int
set @i=1
while @i<=320
begin
exec ('alter table message add a'+cast(@i as varchar)+' varchar(20)')
exec ('alter table message add a'+cast(@i as varchar)+'_reader varchar(20)')
set @i=@i+1
end

[sdhdy]

--更正
declare @i int
set @i=1
while @i<=320
begin
exec ('alter table message add a'+@i+' varchar(20)')
exec ('alter table message add a'+@i+'_reader varchar(20)')
set @i=@i+1
end