Dynamically add 50 columns to table variable

Question

I would like to add around 50 columns to table defined as variable. Names of those columns are coming from another table and basically they are just numbers - DEPA_KEY. (department key)

Is it possible to add those columns dynamically with loop or something similar?

Table defined as variable (here I would like dynamically add 50 columns):

DECLARE @USERS TABLE
(
  USER_KEY INT,
  USDE_HSU DECIMAL(8,2)
)

Query for all departments:

SELECT DEPA_KEY FROM CADEPA

score 10 · Accepted Answer · answered Feb 15 '17 at 09:39

10

AFAIK, You can't change the structure of a table variable:

DECLARE @T AS TABLE
(
    col1 int
);

ALTER TABLE @T 
    ADD col2 char(1)       
;

This will generate an error.

You can, however, do it with a temporary table:

CREATE TABLE #T 
(
    col1 int
);

ALTER TABLE #T 
    ADD col2 char(1)
;

answered Feb 15 '17 at 09:39

Zohar Peled

79,642
10
69
121

The downside is that temporary tables can't be used in functions, only in procedures, so you can't reuse code. – Johan Donado B. Sep 22 '22 at 13:43

Serge · Answer 2 · 2017-02-15T09:45:40.670

It is not possible to change the structure of a table variable. As such, you will not be able to add columns to @USERS.

You can change the structure of a temporary table. If you change your variable to a temporary table, you will then be able to add the columns dynamically:

CREATE TABLE #USERS
(
  USER_KEY INT,
  USDE_HSU DECIMAL(8,2)
)

DECLARE  @col_name VARCHAR(100)
        ,@Sql VARCHAR(MAX)
DECLARE col_name CURSOR
    FOR
    SELECT DEPA_KEY
    FROM CADEPA

OPEN col_name
FETCH NEXT FROM col_name
    INTO @col_name

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @Sql = 'ALTER TABLE #USERS ADD ' + QUOTENAME(@col_name) + ' INT NULL'

    -- PRINT @Sql
    EXEC (@Sql)

    FETCH NEXT FROM col_name
        INTO @col_name

END

CLOSE col_name
DEALLOCATE col_name

Dynamically add 50 columns to table variable

2 Answers2

Linked