动态添加 50 列到表变量

问题描述

我想向定义为变量的表中添加大约 50 列.这些列的名称来自另一个表，基本上它们只是数字 - DEPA_KEY.(部门键)

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?

表定义为变量(这里我想动态添加 50 列):

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

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

查询所有部门:

SELECT DEPA_KEY FROM CADEPA

推荐答案

AFAIK，你不能改变表变量的结构:

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

DECLARE @T AS TABLE
(
    col1 int
);

ALTER TABLE @T 
    ADD col2 char(1)       
;

这会产生一个错误.

您可以，但是，使用临时表来做到这一点:

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

CREATE TABLE #T 
(
    col1 int
);

ALTER TABLE #T 
    ADD col2 char(1)
;