cursor fetch into TABLE_VARIABLE ?

Question

cursor fetch into TABLE_VARIABLE ?

hbkdba

Ten Centuries

Points: 1224
More actions
August 1, 2003 at 11:21 pm

#82496

There's something like TABLE variable in SQL server, could this be used as a data-structure to fetch cursor data into ? (I don't want to declare and use 250+ variables for each table)
--text cut out of MSDN: ---
Remarks
Functions and variables can be declared to be of type table. table variables can be used in functions, stored procedures, and batches.
Use table variables instead of temporary tables, whenever possible. table variables provide the following benefits:
A table variable behaves like a local variable. It has a well-defined scope, which is the function, stored procedure, or batch in which it is declared.
Within its scope, a table variable may be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, table may not be used in the following statements:
INSERT INTO table_variable EXEC stored_procedure
SELECT select_list INTO table_variable statements.
table variables are cleaned up automatically at the end of the function, stored procedure, or batch in which they are defined.
CHECK constraints, DEFAULT values and computed columns in the table type declaration cannot call user-defined functions.
table variables used in stored procedures result in fewer recompilations of the stored procedures than when temporary tables are used.
Transactions involving table variables last only for the duration of an update on the table variable. Thus, table variables require less locking and logging resources.
August 4, 2003 at 8:00 am

This was removed by the editor as SPAM

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

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

hbkdba Ten Centuries Points: 1224 More actions · Answer 1

quote:
No one has responded to this topic yet. Even if you don't have a complete answer, the original poster will appreciate any thoughts you have!

Thanks, indeed I'm eager to know the solution

bcb Old Hand Points: 391 More actions · Answer 2

bcb

Old Hand

Points: 391

August 6, 2003 at 6:40 pm

#468909

I always just fetch into temp tables.

gljjr SSChampion Points: 10080 More actions · Answer 3

The simple answer is no you can't do that.

You need to think of "Table Variables" instead as a temporary table that is stored in memory instead of the tempdb. You then treat the table variable in the same way as you would treat any temp table.

Gary Johnson

Microsoft Natural Language Group

DBA, Sr. DB Engineer

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.