Solved

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

Posted on 2009-07-09
1
315 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL Grouping 2 48
Insert values are dynamic 11 51
date diff with Fiscal Calendar 4 55
mysql query for sum() 3 28
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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 video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

860 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