adding columns to an existing table

Question

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

codingbadger · Accepted Answer · 2011-03-17T09:40:38.847

4

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

edited Mar 17 '11 at 09:40

answered Mar 17 '11 at 09:35

codingbadger

42,678
13
95
110

Sachin Shanbhag · Answer 2 · 2011-03-17T09:43:15.533

1

Use dynamic sql execution-

SET @str = 'ALTER TABLE Info ADD column' + CAST(@counter as varchar(5)) + ' varchar(max)';
EXEC ( @str );
GO

edited Mar 17 '11 at 09:43

answered Mar 17 '11 at 09:37

Sachin Shanbhag

54,530
11
89
103

1

A quick edit is required as you will need to explicitly convert `@counter` to a varchar for this to work :) – codingbadger Mar 17 '11 at 09:42
@Barry - yes. Thank you. Always forget that conversion for dynamic concat. – Sachin Shanbhag Mar 17 '11 at 09:44

score 0 · Answer 3 · answered Mar 17 '11 at 09:36

0

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

answered Mar 17 '11 at 09:36

Andrei Andrushkevich

9,905
4
31
42

adding columns to an existing table

3 Answers3

Linked