Solved

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

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Creating and Managing Databases with phpMyAdmin in cPanel.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

685 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