August 12, 2010 at 6:05 pm
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?
August 12, 2010 at 7:41 pm
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
August 12, 2010 at 8:31 pm
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
August 12, 2010 at 11:05 pm
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
August 13, 2010 at 12:34 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply