How to copy one custom list to another custom list (approximately 600 items)

Question

I have one master list from which I wish to query and create a subset of this in a new custom list. The code I have so far works, but it is extremely slow and occasionally hits the hard limit of the 30 second request time of a sandboxed solution.

The master list contains about 700 items and I will need to copy up to 500 to the subset list.

Is there a more efficient way of running this query? What is the best practice for this sort of task?

                string filterQuery = "<Where><Eq><FieldRef Name='milestone' /><Value Type='Text'>4</Value></Eq></Where>";

                SPQuery query = new Query(); 
                query.Query = filterQuery;
                SPList list = web.Lists["Projects"];
                SPListItemCollection collListItemSrc = list.GetItems(query);
                SPListItemCollection collListItemDest = web.Lists[documentTitle].Items;

                string projectId = "Project name";

                foreach (SPListItem oListItemSrc in collListItemSrc)
                {

                    SPListItem oListItemDest = collListItemDest.Add();


                    oListItemDest["ProjectID"] = projectId;
                    oListItemDest["ProjectName"] = oListItemSrc["ProjectName"];
                    oListItemDest["Owner"] = oListItemSrc["Owner"];
                    oListItemDest["Manager"] = oListItemSrc["Manager"];
                    oListItemDest["Objective"] = oListItemSrc["Objective"];
                    oListItemDest["Duration"] = oListItemSrc["Duration"];
                    oListItemDest["Milestone"] = oListItemSrc["Milestone"];
                    oListItemDest.Update(); 
                 } 

Answer 1

If it is one time operation i.e. Static, you can:

1- Make a list view of the source list with your filters
2- Export the view to Excel
3- Make a datasheet view of the destination List with matching columns to Excel Spreadsheet
4- Copy data from Excel sheet to Datasheet view of List!

OR

Use Excel Add-in to synchronize data from Source Excel to destination List!

If it is more than one time operation i.e. Dynamic:

Have a look at List Item Performance - Updating Large Lists and SharePoint: Using Batch-Updates to speed up performance? ... I don't really know if RPC calls/Batch Adding items work in Sandbox environment or not, but you can give it a try!

Have a look at How to: Update List Items, they say update but of course can be used for Adding items!

EVEN THAT DOESN'T SOLVES THE PROBLEM

You can then schedule this task for Timer Job, keep a list with the user specified criteria and use it as a scheduled task for Timer Job... You can also notify the user about completion of task from within the Timer Job! I usually prefer this approach if the task is time consuming, and keep user posted about this! Let me know if you want more information on this!

I hope this helps!

Answer 2

James,

What about just creating a new View for the current list, which filters to show only what you need?

Or if you don't need all the columns, perhaps creating a second list with specific Lookup columns referencing the first?