Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
 Posted - 2007-10-31 : 11:46:50
|
| I know there's a way to traverse through tables without declaring cursors, but the method escapes me. Essentially, I want to execute a regular select statement, since it's less overhead anyway; it'll return a 1x2 table (meaning 1 row and 2 columns), at which point I can take the values and set them to variables. It's easy to do with cursors using fetch next from example_cursor into... I guess I'm asking what are other ways to fetch data from tables and manipulate them in the form of T-SQL variables w/o using cursors?Thank you. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-10-31 : 11:49:40
|
| What do you want to do with them.It's better not to loop through the rows at all.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2007-10-31 : 12:03:55
|
| I'm working with two tables at the moment. I'm traversing through Table A with a cursor, retrieving multiple column data, one of which will be dereferenced into a T-SQL variable. That is, I'm pointing to the first record in Column 1, retrieving the value and setting it to a variable. Using this variable, I will make a query on Table B, which will output a 1x2 table as described before. Then I wish to take the values returned by this query and store them into variables. Again, the key is not using cursors, especially since this will repeated row-by-row, meaning the second query will always return one record. Is there another method out there for taking table data and storing into variables without cursors? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-10-31 : 12:17:53
|
| I think the key would be not using variables - you are thinking in row by row terms.>> I'm traversing through Table A with a cursor, retrieving multiple column data, one of which will be dereferenced into a T-SQL variable. That is, I'm pointing to the first record in Column 1, retrieving the value and setting it to a variable. Using this variable, I will make a query on Table BChange this torun a query referencing Table A and Table B to produce a resultset.Then see if you can do the same thing with whatever ersult you want.>> Is there another method out there for taking table data and storing into variables without cursors?Don't do it - there's usually no point. Look at a redesign instead.If you want to do this then use an application language rather than sql.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2007-10-31 : 12:40:27
|
| I should've finished my thought lol. I apologize. I will need variables because I must perform manipulation on the data (i.e. calculations, error checking, if else conditionals) and then insert it back into Table A. There is an issue with combining certain tables in the database I'm currently using that adds a layer of considerable complexity. Moreover, this procedure will be expanded upon to include more than 2 tables in the near future. In this case, performing row-by-row operations, in my estimation, probably is my best option...as far as the cursors, couldn't I do something like this:Select @testnum = NumberColumn, @testchar = CharColumn From Table Ato store column data into a variable? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-10-31 : 13:33:38
|
| Nothing you have said requiers the use of variables.>> couldn't I do something like this:>> Select @testnum = NumberColumn, @testchar = CharColumn From Table Anot reallySelect top 1 @testnum = NumberColumn, @testchar = CharColumn From Table AorSelect @testnum = NumberColumn, @testchar = CharColumn From Table A where uniquefield = @somevalueBut it will be very slow.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
traversing through table without using cursors?