SQL Svr and Temp Tables

[glenninflorida]

I'm currently working in an CR 8.5 Environment and have various data sources including a few different SQL Svr 2000 dbs.

I have a possible future opportunity that has a requirement of understanding using temp tables for CR data access.

I have read articles (spent a good few hours googling and reading before this post) on using temp tables in SQL Server to to produce CR data but - maybe it is by design the intended simplicity of the articles - I cannot see the use of a temp table over some set operations in a select statement.

In an SP, I've joined various different datasources (using linked servers), created unions, converted datatypes when necessary, converted data (using case statements), and passed parameters.

The only scenario I can think of is maybe having a temp table which combines both detail and summary data (the summary records built on the fly in the SP then inserted into the tables. I am warm?

Can anyone give a solid example of using temp tables to perform CR data access that either can't be or shouldn't be performed by unions, case staements, and other set operations?

Thank you for your time,
Glenn

 

[satinsilhouette]

Speed of processing. And I wouldn't use a temp table, I would have the sp create a view for CR to access.

Keep everything you can on the SQL server, let CR handle the rest.