Dynamically add 50 columns to table variable

问题:

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

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

回答2:

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