CODEWITHSUNDEEP
sqlsql-server
Pr0no
Pr0no

Reputation: 4099

How to easily add columns to a temp table?

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?

Upvotes: 4

Views: 27991

Answers (3)

Mihai Cristian
Mihai Cristian

Reputation: 125

This is the way i like to use this type of adding column and update after:

select *
into #tmp
            from openquery(PARADOX_SRV_TEST, 'select * from D:\WinMent\DATA\TESTP\Npart.DB ') p


            ALTER TABLE #tmp ADD District nvarchar(10), SimbolClasa nvarchar(100)

            Update t 
            set t.District = (Select District from Cities c where c.Id = t.Localit)
            from #tmp t

            Update t 
            set t.SimbolClasa = (Select ISNULL(Simbol,'ND') as Simbol from CustomersCategory c where c.Cod = t.Clasa)
            from #tmp t

            select *,  ISNULL(c.Simbol,'Nedefinit') as Simbol from #tmp t 
            LEFT JOIN CustomersCategory c on c.Cod = t.Clasa

If i use this type of adding:

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

sometimes i recive this type of error: 

Msg 8152, Level 16, State 14, Line 8
String or binary data would be truncated.
The statement has been terminated.

Upvotes: 0

Pradeep Kumar
Pradeep Kumar

Reputation: 6969

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.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269533

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;

Upvotes: 6

Related Questions