How to easily add columns to a temp table?

Question

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?

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

Answer 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.

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.