Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Create #Temp Table using While Loop Instead of Cursor Expand / Collapse
Posted Thursday, August 12, 2010 6:05 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 18, 2016 6:54 AM
Points: 238, Visits: 332

I want to create a temporary table using a While Loop instead of a Cursor. The first step in the process retrieves the column names from the SysColumns tables and the name of the columns are inserted into a #Temp table. The first #Temp table is created with a identity field to so the code can loop through while the counter variable is less than the max ID from the temporary table with the column names. When I run a select to see if the code is actually looping through the column names, the select statement only displays the last column from the temporary table containing the column names.

Does anyone have any examples of creating a table dynamically using a WHILE Loop?
Post #968622
Posted Thursday, August 12, 2010 7:41 PM



Group: General Forum Members
Last Login: Yesterday @ 4:15 PM
Points: 2,397, Visits: 9,463
It's really not clear what you are trying to accomplish. Are you trying to replicate the Information_Schema.Columns view? How about providing sample data and expected results as recommended in the Forum Etiquette


J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

How to post data/code on a forum to get the best help.
Make sure that you include code in the appropriate IFCode tags, e.g. [code="sql"]<your code here>[/code]. You can find the IFCode tags on the left when you are writing a post.
How to Post Performance Problems
Post #968630
Posted Thursday, August 12, 2010 8:31 PM



Group: General Forum Members
Last Login: Monday, November 21, 2016 11:03 AM
Points: 20,009, Visits: 18,255
I would really like to see a more clear definition of the requirements. There is likely an alternative set based method to accomplish your goals.

Could you provide greater detail?

Jason AKA CirqueDeSQLeil
I have given a name to my pain...


Posting Performance Based Questions - Gail Shaw
Post #968634
Posted Thursday, August 12, 2010 11:05 PM


Group: General Forum Members
Last Login: Tuesday, April 23, 2013 7:42 AM
Points: 118, Visits: 743
check if you are looking something like below....if not, post table defintions ,sample data and expected results to get faster and accurate responses.

if object_id('tempdb..#test123') is not null
drop table #test123


select * from #test123

-- Jus

For better assistance in answering your questions
Perforamance Issues
Cross Tabs and Pivots
Cross Apply
The Numbers or Tally Table- Jeff Moden
Post #968656
Posted Friday, August 13, 2010 12:34 AM



Group: General Forum Members
Last Login: Saturday, December 3, 2016 5:18 AM
Points: 45,619, Visits: 44,147
kscott-851323 (8/12/2010)
I want to create a temporary table using a While Loop instead of a Cursor.

Why? If you're trying to remove the cursor, a while loop is no better. Just another form of row-by-row processing.

Is the end goal to use a cursor/loop, or is the end goal to populate a table?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #968677
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse