sql add column with variable names

Question

Is there any way to add variable number of columns to a table.

I am trying below but no success.

DECLARE @VAR INT = 1
WHILE @VAR <=20
BEGIN
    DECLARE @COLUMN VARCHAR(MAX) = 'COLUMN_'+CAST(@VAR AS VARCHAR)
    IF NOT EXISTS(SELECT 1 FROM SYS.columns WHERE OBJECT_ID = OBJECT_ID('MY_TBL') AND name=@COLUMN )
        begin
        ALTER TABLE MY_TBL ADD @COLUMN NVARCHAR(3) NULL;
        end
    SET @VAR += 1
END

That's a really bad table design. Why are you embedding data in the metadata (column names). It will also lead to convoluted and confusing queries. The standard way of representing repeating groups of the same "type" of data is as multiple rows of data. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Apr 6, 2017 at 6:17
This is bad design. If you have lots of columns with the same type data, make multiple rows instead. Thats what they're for. — Magisch
– Magisch, Commented Apr 6, 2017 at 6:18
Summing up with previous guys here. This is a terrible design. — Jorge Campos
– Jorge Campos, Commented Apr 6, 2017 at 6:20

Shival · Accepted Answer · 2017-04-06 06:21:12Z

2

You need to use dynamic SQL. Try the following code.

DECLARE @VAR INT = 1
WHILE @VAR <=20
BEGIN
    DECLARE @COLUMN VARCHAR(MAX) = 'COLUMN_'+CAST(@VAR AS VARCHAR)
    IF NOT EXISTS(SELECT 1 FROM SYS.columns WHERE OBJECT_ID = OBJECT_ID('MY_TBL') AND name=@COLUMN )
        begin
        declare @sql varchar(100)= ' ALTER TABLE MY_TBL ADD '+@COLUMN+' NVARCHAR(3) NULL;' 
        exec(@sql);
        end
    SET @VAR += 1
END

answered Apr 6, 2017 at 6:21

Shival

2741 gold badge2 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kung Fu Panda · Accepted Answer · 2017-04-06 06:22:16Z

-1

For SQL-Server:

Format:

SELECT COUNT(*) 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_CATALOG = 'database' AND TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'table'

Example:

SELECT COUNT(*) 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_CATALOG = 'imDB' AND TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'MovieName'

You can store it in a variable and use it accordingly.

I hope you understood :)

answered Apr 6, 2017 at 6:22

Kung Fu Panda

64610 silver badges22 bronze badges

2 Comments

Brauer Suzuki Over a year ago

How does this answer the question?

Kung Fu Panda Over a year ago

I don't know what was in his mind when he wrote this answer 6 year ago.

Collectives™ on Stack Overflow

sql add column with variable names

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related