Create #Temp Table using While Loop Instead of Cursor

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

  • 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

  • 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

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

  • 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

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

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