How to easily add columns to a temp table?

user_330271 0
Viewed 21087

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?

sql-server sql
3 Answers

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.
user_105910450
Related