The answer is to not use a temp table at all. This query can be written AND run much more efficiently by using...
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
what is referred to as a virtual table. It looks something like this:
Select col1, sum(col2) testsum Into #temp >From table1 Group by col1 Select col1, col4, sum(testsum) secondaryagg >From table2 inner join #temp on table2.col1 = #temp.col1 Group by col1, col4VIRTUAL TABLE:
Select col1, col4, sum(testsum) secondaryagg >From table2 inner join (Select col1, sum(col2) testsum From table1 Group by col1) a on table2.col1 = a.col1 Group by col1, col4
For More Information
- Dozens more answers to tough SQL Server questions from Michael Hotek are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on SQL Server Stored Procedures
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.