如何轻松地向临时表添加列?

I can add any number and types of columns into a temp table without the need to define them first:

我可以将任何数量和类型的列添加到临时表中，而不需要首先定义它们:

select into #temp from table;

But if I want to add columns to this temp table later on in my script, the only way I know how is to:

但是如果我想在脚本后面的临时表中添加列，我知道的唯一方法是:

alter #temp add column int;
insert into #table (column) select column from table;

This is a bit cumbersome if I want to add multiple columns. Is there a way to add column to a temp table without defining them first?

如果我想添加多个列，这有点麻烦。是否有一种方法可以将列添加到临时表中而不首先定义它们?

2 个解决方案

#1

I don't think insert is appropriate after adding a column. Update seems more like the operation you would want.

我认为在添加一列之后插入是不合适的。更新看起来更像是您想要的操作。

One option is to create a new temporary table:

一种选择是创建一个新的临时表:

select t.*, 'value' as col
into #temp1
from #temp t;

However, for an existing table, there is no way to add a column and populate it at the same time -- except for providing a default value.

但是，对于一个现有的表，除了提供一个默认值之外，没有办法同时添加一个列并填充它。

You can, however, add multiple columns at the same time:

但是，您可以同时添加多个列:

alter #temp add col1 int, col2 int, col3 int;

update #temp t
    set col1 = 1, col2 = 2, col3 = 3;

#2

It is almost certain you know how many columns you finally need. You can create the extra columns (not present in your table/query result) with dummy constant values when creating your #temp table.

几乎可以肯定，您知道最终需要多少列。在创建#temp表时，可以使用假常量值创建额外的列(不在表/查询结果中)。

e.g.

如。

select *, '' as AdditionalStringColumn into #temp from table1;

select *, 0 as AdditionalIntegerColumn into #temp from table1;

select *, 0.0 as AdditionalDecimalColumn into #temp from table1;

This way you don't need to get into the mess of dealing with alter table etc., and will have better performance.

这样，您就不需要陷入处理alter table等问题的混乱之中，并且可以获得更好的性能。

#1