How to dynamically create coloumns in temporary table

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

Latest Posts