Dynamic create table

Hi,

I need to create a table....whose column names should be dynamic...I wouldnt know in advance what the column names would be...These coulmn names are availabe in another table as rows...

Ex: TABLE_A Data

Column Names

Col1

Col2

Col3

Col4...

Some column Names are known already...I need help in writinf the Create table statement..

I need to write something like

Create table Table1

(SerialNo int,Name nvarchar(50)+ AS select * from TABLE_A +

Address nvarchar(500))....

Now the Table1 should look like

Serial No Name Col1 Col2 Col3 Col4 Address

Can some one please let me know how can i accomplish this...I know i need to write a Dynamic Query but dont exactly know ho to write it and execute it....

Thanks

You need to create your "CREATE TABLE.." statement dynamically. How would a user select the columns? If you dont know the column names do you know what datatype they should be?|||

Yeah I know the DataType they are all of float datatype...

But the rows in TableA may change so I need to create a temporary table with the column names with how many ever rows are available in the TableA.

Eventually in the stored proc I am going to drop this Temporary table after selecting the Data from it....

|||Can you please let me know if i can create a table by using both as select and some already knowm column names|||

something like that ?

declare @.sql nvarchar(2000)

set @.sql = N'create table myTempTable ( id int identity, name varchar(100) );'

execute sp_executesql @.sql

|||

You can try this .It's working correctly.

Create proc prcTempTab

AS
SET NOCOUNT ON
declare @.j int
set @.j=1
declare @.strtemp varchar(1000)
declare @.c varchar(4)
Create table #temptab(i int)
while(@.j<4)
begin
set @.c='a'+convert(varchar(2),@.j)
set @.strtemp='ALTER TABLE #temptab ADD '+@.c+' int'
set @.j=@.j+1
exec (@.strtemp)
end

insert into #temptab values(1,2,3,4)
select * from #temptab

--exec prcTempTab

All the best

|||Good work Sarika, appreciable