Writing view to table - fastest/easiest way?
Writing view to table - fastest/easiest way?
(OP)
I have a view that returns approximately 5 million rows and is growing at the rate of several thousand rows per month. I'm using a reporting tool that crashes when I try to report off the view. I tested writing the view to a table and reporting off the table, and the reporting tool works fine when I report off the table created from the view. So I would like to automate what I did manually and run reports off the table.
I used Import Data in SQL 2005, and it took about 4 hours for the 5 million rows to write from the view to the table. I need to schedule this to update the table about 3 times a week in advance of running the reports. I don't know of a way easily identify new/changed records to write ONLY those to the table because there are many tables and views underlying the view I want to write to the table. So I think I will have to re-write the entire table every time.
What is the most efficient/fastest way to schedule this to run?
Thanks in advance for your help.
I used Import Data in SQL 2005, and it took about 4 hours for the 5 million rows to write from the view to the table. I need to schedule this to update the table about 3 times a week in advance of running the reports. I don't know of a way easily identify new/changed records to write ONLY those to the table because there are many tables and views underlying the view I want to write to the table. So I think I will have to re-write the entire table every time.
What is the most efficient/fastest way to schedule this to run?
Thanks in advance for your help.
Talk To Other Members
RE: Writing view to table - fastest/easiest way?
Secondly, four hours to copy five million rows to a table from a view seems like a long time, unless you have an extremely wide table. You'll probably get the most benefit as far as speed is concerned by optimizing your query which defines the view, and possibly adding and/or maintaining your indexes and statistics. You should be able to get the four hours down to something more reasonable.
Thirdly, to do the actual insert, your easiest solution would be something like:
CODE
INSERT INTO YourTableName SELECT * FROM YourViewName;
CODE
SELECT @LastUpdatedDate = LastUpdatedDate FROM SomeMetadataETLTable WHERE TableName = 'YourViewName'
DELETE YourTableName WHERE LastUpdatededDate >= @LastUpdatedDate;
INSERT INTO YourTableName SELECT * FROM YourViewName WHERE LastUpdatedDate >= @LastUpdatedDate;
UPDATE SomeMetadataETLTable SET LastUpdatedDate = (SELECT MAX(LastUpdatedDate) FROM YourTableName) WHERE TableName = 'YourViewName';
With the incremental load solution, you would either have to maintain a table to keep track of the date you wish to load, or use some more basic logic such as "load everything from the past week."
RE: Writing view to table - fastest/easiest way?