How to update table nightly from massive view

[hblabonte]

Hi all,

I've got a very invloved view joining quite a number of tables. It takes 30+ minutes to run because of all the data we're pulling.

To make things easier on the user, I'd like to populate a temp table each night with this data (and report from that). I'm not sure how to go about this as I haven't done much with stored procedures.

any tips?

 

[JohnDTampaBay]

Assuming your view's SELECT statement contains all the data that you want in your reporting table, the stored procedure to repopulate the reporting table might look something like this:

CREATE PROC uspMakeReportingTable
AS
  IF EXISTS(SELECT * FROM Information_Schema.Tables
            WHERE Table_Name = 'ReportingTable')
      DROP TABLE ReportingTable

  SELECT *
  INTO ReportingTable
  FROM view

But you really don't need the view at all, if you will be reporting from the table. So take the SELECT from the view, add the INTO clause, and use it in your stored procedure. Good luck!

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[hblabonte]

Thanks John. Quick question to clarify. Do I need to name the columns in my view the same as in my temp table?

I'm wondering how that select into statement would work otherwise.

Thanks again,
Heather

 

[JohnDTampaBay]

Using the code above, the column names in the view will become the column names in the reporting table. If you want different column names in the table, you will have to alias each column in the SELECT clause. Can you eliminate the view altogether and put the appropriate SELECT right in the store procedure?

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[SQLSister]

You might also consider looking at your indexing. Do you have indexes on all the join fields (Fks need to have indexesspecifically created, PKs get created when they are defines as PKs.)

BUt yes get rid of the view and act on the tables directly, it will be more efficient. SInce you have a lot of joins, I'd steal teh SQL for the joins and paste it into the SP to save time recreating it. But no real need to use the view at all if yo uare using an sp.

Questions about posting. See faq183-874