How to dynamically create coloumns in temporary table

Posted by Amritha444 under Sql Server on 7/25/2011 | Points: 10 | Views : 4480 | Status : [Member] | Replies : 3
hi all

i want to know how to bind one table fields as coloums of temporary table
eg:- i have one table fields as A,B,c....like

id name
1 A
2 B
3 C

this table content may edited ,deleted ,added
i want those name fields as row..i cant give static values while creating temporary table as the contet of table may get changed

CREATE TABLE #TEMP_Branch (
Branch VARCHAR(50) ,A,B.C.....)

how to create temporary table


Thanks in Advance




Responses

Posted by: PandianS on: 7/25/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

Kindly follow the scenario

1.Creating one schema table
Create Table TableSchema

(
Id Int Identity(1,1),
Name Varchar(50),
DataType Varchar(50)
)
Go
2. Inserting schema info.,
INsert TableSchema Values('A','Varchar(5) NULL')

INsert TableSchema Values('B','Varchar(10) NULL')
INsert TableSchema Values('C','DateTime NULL')
INsert TableSchema Values('D','Int NULL')
Go
3. Create Temp table based on the schema data
Declare @Statement	Varchar(1000)

Select @Statement = 'Create Table ##TEMP_Branch(Branch Varchar(50)'
Select @Statement = COALESCE(@Statement +',','') + Name + ' ' + DataType from TableSchema
Select @Statement = @Statement + ')'
EXEC(@Statement)
Select * from ##TEMP_Branch


Note: Here, we have to use Global(##) Temp table instead of Local(#), Because, we have created the Temp table inside the batch/script (Dynamic Script), so, It can be accessed within the block only... If we want to use the Temp table outside the block then... You have to use Global Temp(##) table.. (or) You can use the Local Temp table ONLY within the string here(Dynamic script)... :)


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Msahoo on: 7/25/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,
To create temporary Table use below code :
CREATE TABLE #TempTable1

(
tempfield1 INT,
tempfield2 VARCHAR(50)
)

For more information check my full post here :
http://dotnetsquare.com/resources/50-temporary-tables-in-sql-server


Thank You.

Regards,
Manoranjan Sahoo
http://www.dotnetsquare.com

Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Yoosufshanij on: 12/20/2011 [Member] Starter | Points: 25

Up
0
Down
check this out:
http://blog.yoosufshanij.com/2011/08/how-to-dynamically-create-table-using.html

Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response