I need to add around 600 columns to an existing table for testing purpose
declare @counter int
set @counter = 1
while @counter < 601
begin
ALTER TABLE Info ADD column+@counter varchar(max);
set @counter = @counter + 1
end
The column name should looks like column1,column2....column600
As this is purely for testing a quick and dirty way is to do this:
declare @counter int
set @counter = 1
declare @sql varchar(2000)
while @counter < 601
begin
Set @Sql = 'ALTER TABLE Info ADD column'+convert(varchar(10),@counter)+ ' varchar(max)'
Exec (@Sql)
set @counter = @counter + 1
end
Although I have to wonder why you would want 600 columns in a table
Use dynamic sql execution-
SET @str = 'ALTER TABLE Info ADD column' + CAST(@counter as varchar(5)) + ' varchar(max)';
EXEC ( @str );
GO
@counter to a varchar for this to work :)
use Exec function:
Declare @SQL VarChar(1000)
Declare @vary varchar(100)
Declare @final varchar(1000)
SELECT @SQL = 'ALTER TABLE '
SELECT @SQL = @SQL + @TableName
select @vary = 'add column ' + id
select @final = @sql + @vary
Exec ( @final)
GO
