Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
Author
Message
Posted Thursday, August 12, 2010 6:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:36 PM
Points: 157, Visits: 251
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?
Post #968622
Posted Thursday, August 12, 2010 7:41 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 8:24 AM
Points: 1,240, Visits: 5,421
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
Post #968630
Posted Thursday, August 12, 2010 8:31 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:32 PM
Points: 18,055, Visits: 16,087
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
Post #968634
Posted Thursday, August 12, 2010 11:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

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 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
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


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:35 AM
Points: 40,605, Visits: 37,059
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 2008, MVP
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