往sqlserver中动态添加字段 sqlserver动态添加字段

drop table test

create table test
(
	id int primary key identity(1,1),
	name varchar(20)
);

alter table test add clm_t1 nvarchar(20) null
alter table test drop column ['+@dateStr+']

select * from test;

declare @startYear int,@endYear int;
declare @startMonth int,@endMonth int
declare @dateStr nvarchar(20)
declare @yearStr nvarchar(20)
declare @monthStr nvarchar(20)

set @startYear = 2000
set @endYear=2011
set @startMonth=1
set @endMonth=12

while(@startYear<=@endYear)
begin
	while(@startMonth<=@endMonth)
	begin
		set @yearStr = convert(nvarchar,@startYear,4)
		if(@startMonth<10)
		begin
			set @monthStr = '0'+convert(nvarchar,@startMonth,2)
		end
		else
		begin
			set @monthStr = convert(nvarchar,@startMonth,2)
		end
		set @dateStr = @yearStr +'-'+@monthStr
		print @dateStr
		exec('alter table test add ['+@dateStr+'] nvarchar(50) null')
		set @startMonth = @startMonth+1
	end
	set @startYear = @startYear+1
	print convert(nvarchar,@startYear,4)
end