[SOLVED] Alter dynamically created table in ms sql

Alter dynamically created table in ms sql

Suppose, I have a temp table #Temp1 with say two columns Name and Nationality as Varchar(50) datatypes and the table has some records. I create another temp table from this table(#temp1) as #Temp2. Now I wish to add another couple of column to that temp table say PhoneNo as Int and Gender as Char datatypes. How do I achieve that?

Begin Tran
Create table #Temp1(
    Name    Varchar(50),
    Nationality Varchar(50)
);

Insert Into #Temp1 (Name, Nationality) Values ('Jayaraj','Indian');

Select Identity(Int,1,1) SlNo, Name Into #Temp2
From #Temp1 Order By Nationality    

-- Now the actual issue begins. I wish to alter the #Temp2 table.
-- So I try to alter the table, to add column Phone and Gender

Alter Table #Temp2
Add(
    Phone Int,
    Gender Char
);

Select * from #Temp2

-- Upon Executing I get this error : 
/*
  Msg 102, Level 15, State 1, Line 16
  Incorrect syntax near '('.
*/

Rollback

Thanks for helping..

Solution

You can do this:

Alter Table #Temp2
Add Phone Int, Gender Char

Just have to remove "()". :-)