Hello, I am not sure where to start with this as I have never had to run report against a temp table before. I have wrote what I believe is a beautiful query to pull a ton of data into from MANY different tables to accommodate a request from administration. I then wrote a select query to pull in the data they need.
How do I set this up to run every day? I don't believe I can create the temp table right in my query written for the report in visual studio, can I? Do I need to set something up in SSMS for the temp table and then have the VS report run after the temp table is updated every day? Thanks
You need to be careful about pulling in a ton of data from many different tables. Just a few questions off the top of my head:
This can have negative impact on other users if you are locking tables, etc.. Is this a report through SSRS or are you running it and exporting to excel or something like that? Does this run on off hours? How are parameters setup to run every day (i.e. does it run for just the previous day of all of history or some timeframe in between?). How is report distributed?
I'm sure it is a beautiful query, but have you looked at the execution plan to make sure? how long does it take to run and how much data is it bringing back?