Solved

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

Posted on 2009-07-09
1
311 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
Comment Utility
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 Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now