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

Answer 1

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)
;