Create #Temp Table using While Loop Instead of Cursor

Question

Create #Temp Table using While Loop Instead of Cursor

kscott-851323

Right there with Babe

Points: 759
More actions
August 12, 2010 at 6:05 pm

#239019

Hello,
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?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76944 More actions · Answer 1

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 http://www.sqlservercentral.com/articles/Best+Practices/61537/?

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

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...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

NewBeeSQL SSCrazy Points: 2170 More actions · Answer 3

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 id1 = IDENTITY( int,1,1),table_name,COLUMN_NAME, DATA_TYPE, IS_NULLABLE

into #test123 FROM INFORMATION_SCHEMA.COLUMNS

select * from #test123

-- Jus

-----------------------------------------------------------------------
For better assistance in answering your questions[/url]
Perforamance Issues[/url]
Cross Tabs and Pivots[/url]
Cross Apply[/url]
The Numbers or Tally Table- Jeff Moden[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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