tags:

views:

305

answers:

2
+2  Q: 

TSQL question : how to iterate columns of result set

Hi,

I have a select statement and a cursor to iterate the rows I get. the problem is that I have many columns (more than 500), and so "fetch .. into @variable" is impossible for me. how can I iterate the columns (one by one, I need to process the data)?

Thanks in advance, n.b

+1  A: 

Two choices.

1/ Use SSIS or ADO.Net to pour through your dataset row by row. 2/ Consider what you're actually needing to achieve and find a set-based approach.

My preference is for option 2. Let us know what you need done and we'll find a way.

Rob

Rob Farley  2009-07-29 07:21:39
That what I did .. I have one table which get the data from a flat file using SSIS. now I need to process the data (validation, matching etc.) and iterating the columns seems necessary for it.n.b
 2009-07-29 07:52:24
nab, you never need to iterate to do validation or matching. In fact you almost never need to iterate to do anything. I fyou are using a cursor, that is your first clue to look for a better way to do things. I import data all the time and do all data cleanup with set-based code which is usually much faster than iteration. Or you can do all the data validation in your SSIS package.
HLGEM  2009-07-29 17:06:28
nab - you'd've been better off putting the validation in the SSIS package. Then you can send the unvalidated data off to a different table, or through a set of cleansing processes, while the clean data ends up in your table. Rob
Rob Farley  2009-07-30 08:14:01
A: 

You can build a SQL string using sys.columns or INFORMATION_SCHEMA queries. Here's a post I wrote on that.

Jon Galloway  2009-07-29 07:30:22

related questions

*= in Sybase SQL
sql missing rows when grouped by DAY, MONTH, YEAR
Adapt Replace all strings in all tables to work with text
Can you call a webservice from TSQL code?
How to tell the data types after executing a stored procedure?
C# - SQLClient - Simplest INSERT
SQL Server: Get data for only the past year
Get last item in a table - SQL
Is there a way to make a TSQL variable constant?
Create a database from another database?
SQL Server PIVOT examples?
What are some references, lessons and or best practices for SQL optimization training.
What's a good way to check if two datetimes are on the same calendar day in TSQL?
Trigger without a transaction?
How do I do an Upsert Into Table?
What is the easiest way using T-SQL / MS-SQL to append a string to existing table cells?
How do I avoid using cursors in Sybase (T-SQL)?
Best .NET Solution for Frequently Changed Database
T-Sql date format for seconds since last epoch / formatting for sqlite input
HOWTO - Compare a date string to datetime in SQL Server?
Differences Between MySql and MS SQL
Best method for varchar date validation in Sybase (T-SQL)?
interrogating table lock schemes in T-SQL
Testing for inequality in T-SQL
T-Sql Remove Decimal Point From Money Data Type