reusing result table based upon a temp table

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

reusing result table based upon a temp table

Author

Topic

RealityMasque
Starting Member

4 Posts

Posted - 2009-03-13 : 05:34:45

Heya,

I'm not sure how to express this properly, but here goes...

I need to create a temporary table, insert data into some of the columns, then use the inserted data to update the rest of the columns in the temporary table. This is due to legacy table structures, & it's the fastest way I've found to get a table back with the specific data I need.

To make is easy to get this data back, I put it into a stored procedure. However, I now need to get a single record from this table, & I don't want to have to write the entirely same process of creating a temporary table, just to fill it with one record.

I've tried moving my table filling process into a user defined funtion, but it can't handle temporary tables. So I tried changing the temporary table to a table variable, but I cannot insert data into some of the table variable's field, then use that data to update the rest of the fields. I tried using a view, but it has the same limitation as user defined functions with temporary tables.

here's an example of what I've done so far...


create procedure ListResults as

create table #results
(
   id int,
   name nvarchar(20),
   otherdata nvarchar(20)   
)

insert into #results ( id )
select other_table_id from other_table_1

update #results
set name = other_table_2.name
from
   #results
   inner join other_table_2 on other_table_2.id = #results.id

since I already have the means to get this table of data, I would like to reuse that table to get a single record from it...

hope this makes sense, & thanks in advance...

- O8

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-13 : 06:16:37

Not sure if I understand you correctly. But if you want to access the temporary table from other sessions, you can change your temp table to a global temp table and then run a select on it. Just have 2 have two number signs (##) there to convert it into a global temp table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources