Solved

Dynamically add a new column in a temp table for each row in a query

Posted on 2009-07-09
1
313 Views
Last Modified: 2012-05-07
I need to dynamically add a collection of columns to an existing temp table.  The column names are derived from a separate query and all the columns will have the same data type of varchar (100).  Any ideas?
For example see the psuedo-code below.  

CREATE TABLE #fields
(
	column1 int, 
	column2 varchar(100), 
	colume3 varchar(100)
)
 
FOREACH columnName in SELECT columnName FROM tableName WHERE foo = bar
BEGIN
	EXEC 'ALTER TABLE #fields ADD [' + columnName + '] varchar(100)'
END
 
-- Populate table #temp
 
DROP TABLE #temp

Open in new window

0
Comment
Question by:andy_ee
1 Comment
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 24814813
declare @sql varchar(max)
select @sql = COALESCE(@sql+',', '') +COLUMN_NAME +' VARCHAR (100) '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME  = 'Foo'
EXEC( ' ALTER TABLE #Temp ADD '+@sql )
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question