Solved

How to get results with CTE query rahter than Loop

Posted on 2014-02-23
1
346 Views
Last Modified: 2014-02-28
I have created a stored procedure with loops that gives me the output I need, but it is slow. I have tried to use a CTE query, but do not know how to create the TotalSamples. The CTE creates the same number of rows as the looped query, but does not calculate TotalSamples. TotalSamples should be the distinct samplekey by site, collectionYear, germinationYear, plot, habitat
speciesBySiteTeatmentWithLoop.sql
speciesBySiteTreatment.sql
sampleSeedbank50.csv
createSeedbank.sql
siteTreatmentSpeciesSampleOutput.csv
siteTreatmentSpeciesOutputCTE.csv
0
Comment
Question by:dblankman
1 Comment
 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
Comment Utility
Hi Blankman,

A CTE won't necessarily replace a store procedure or the necessity of looping on data.  What the CTE does is allow you to break a complex query into smaller pieces and reuse some of the intermediate results without the database rebuilding those results.

If you can't visualize a query that will do what you want, neither a complex query nor a CTE will do what you want any better than your stored procedure.


Kent
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now