How to migrate list items of two list into the third list?

Question

Suppose I have three lists, List A, List B, List C. I have to migrate list items of List A and List B into List C. How to implement this using CAML and without CAML, which one is better approach. Please suggest me.

Answer 1

Can you check the approach suggested in this discussion below:

http://social.msdn.microsoft.com/Forums/en/sharepointcustomization/thread/5165c798-9db6-4623-a147-03907e99eb57

Also check this link for achieving the same:

http://office.microsoft.com/en-us/sharepointdesigner/HA100991441033.aspx?pid=CH100667641033

---

BR, PM

Answer 2

The caml approach is good if you need to query the lists to get just certain items from a list and then migrate them.  If you just need to move items from a folder in one list to another you can use the following code. The MoveTo method only moves items within the same site collection. The code below will move the folder and its contents to another list.

 

public static void MoveFolderContents(string folderUrl, string destinationFolder)
{

      //folderUrl is absolute url
      //destinationFolder is relative folder url e.g documentlibaryname/foldername

      using (SPSite site = new SPSite(folderUrl))
      {
        using (SPWeb web = site.OpenWeb())
        {
          SPFile sourceFolder = web.GetFile(folderUrl);
          sourceFolder.MoveTo(destinationFolder);
          
        }

      }
 
}

---

certdev.com

Answer 3

Hi Harsh,

List A and List B can both be queried based on some criteria using CAML. Finally the contents of the query can then be pushed into List C.

Answer to your questions -

How to implement CAML (Example) - sorry in VB but you will get the idea

Dim oList as SPList = SPContext.Current.Web.Lists("List A")
Dim caml as SPQuery = New SPQuery
caml.Query = "<Where>" + 
     "<Eq>" + 
      "<FieldRef Name='Company' />" + 
      "<Value Type='Text'>something</Value>" + 
     "</Eq>" + 
     "</Where>" + 
     "<OrderBy>" + 
     "<FieldRef Name='LinkTitle' />" + 
     "</OrderBy>"; 
Dim results as SPListItemCollection = oList.GetItems(caml)

Dim i as integer
Dim value as String

For i=0 to results.count-1
  Dim oItem as SPListItem = results(i)
  value = oItem("Column Name").ToString
Next

In this way you can query both List A and List B based on CAML queries, then simply push the variables (example value) into List C which is like this -
Dim newItem as SPListItem = listC.Items.Add()
newItem("Column Name") = value.toString()
newItem.Update      - this ensures you are migrating into List C

Which is better ?

I would say CAML is better, it is quick efficient and reliable and ensures optimum performance. Otherwise you will be doing standard list iteration and imagine you have a query based on more than 2 criteria on list A and list B also the performance based on the size of these lists. So think about it. CAML queries are like SQL queries much faster. But if you still want to iterate list please follow this link that will explain the correct way of doing so (with performance proof )- http://blog.dynatrace.com/2009/01/11/the-wrong-way-to-iterate-through-sharepoint-splist-items/

Finally please download http://www.u2u.be/res/Tools/CamlQueryBuilder.aspx - CAML builder to make your querying accurate and time saving.

Hope this helps,

Thanks,

Answer 4

Thanks for responding, I don't want to iterate list items one by one, I want to get all items of both lists together like we do in sql using 'join' but join keyword is not possible in the Caml query, so is it possible to do it using SpdataQuery class. if is possible then how? please suggest me.

Answer 5

Below is some example code that uses the SPSiteDataQuery. I have also listed some xml. You can limit your query to the lists you want by adding List elements in the xml that have the ID's of the lists you want to query. This will return a dataset which contains everything you need to move or copy the items to another library.

public static DataTable GetSiteDataItems(string camlQuery)
{
      DataTable retResults = null;

      using (SPSite site = new SPSite("http://basesmcdev2/sites/tester1"))
      {
        using (SPWeb web = site.OpenWeb())
        {

          XmlDocument queryDoc = new XmlDocument();
          queryDoc.LoadXml(camlQuery);

          XmlNode queryNode = queryDoc.SelectSingleNode("//Query");
          XmlNode viewfieldsNode = queryDoc.SelectSingleNode("//ViewFields");
          XmlNode queryOptionsNode = queryDoc.SelectSingleNode("//QueryOptions");
          XmlNode listOptionsNode = queryDoc.SelectSingleNode("//Lists");
          XmlNode websOptionsNode = queryDoc.SelectSingleNode("//Webs");

          SPSiteDataQuery spq = new SPSiteDataQuery();
          spq.Query = queryNode.InnerXml;
          spq.ViewFields = viewfieldsNode.InnerXml;
          spq.Webs = websOptionsNode.OuterXml;
          spq.Lists = listOptionsNode.OuterXml;
          spq.RowLimit = 2147483647;


          retResults = web.GetSiteData(spq);

        }

      }

      return retResults;

}

 

<Caml>
- <Query>
- <Where>
- <And>
- <IsNotNull>
 <FieldRef Name="Title" /> 
 </IsNotNull>
- <Neq>
 <FieldRef Name="FSObjType" /> 
 <Value Type="Lookup">1</Value> 
 </Neq>
 </And>
 </Where>
 </Query>
- <ViewFields>
 <FieldRef Name="FileRef" Nullable="TRUE" /> 
 <FieldRef Name="EncodedAbsUrl" Nullable="TRUE" /> 
 <FieldRef Name="FileDirRef" Nullable="TRUE" /> 
 <FieldRef Name="Title" Nullable="TRUE" Type="TEXT" /> 
 <FieldRef Name="multiuser1" Nullable="TRUE" Type="UserMulti" /> 
 </ViewFields>
 <QueryOptions /> 
- <Lists>
 <List ID="1ad69690-94cf-4471-9298-f81629427acc" /> 
 <List ID="cc22cc7d-db46-4e99-910c-131503057cd6" /> 
 </Lists>
 <Webs Scope="Recursive" /> 
 </Caml>

 

---

certdev.com

Answer 6

Hi Steve,

The code suggested you working fine except one issue. If I have two lists and the fields which I taken as viewfields are different it return 0 row.

suppose I have to display one field named 'EmpName' of List one and field named 'DepartName' of the second List i.e.

<FieldRef Name="'EmpName' " Nullable="TRUE" />
<FieldRef Name="DepartName " Nullable="TRUE" />

It returns no record. If we have both fields EmpName and DepartName in both Lists then it returns all records.

Please give me solution for returning all records if we have to dispaly all records with different fields into lists.

What is the use of Nullable="TRUE".

Answer 7

Nullable = TRUE is to make sure to return a null value if a value does not exist. Are you using any of the fields to filter or sort by? You will only be able to filter and sort on fields that are common among the two lists.

---

certdev.com

Answer 8

Thanks,

If I have four fields in list one and three fields in list two and I want to display data of both 7 fields together and all seven fields are different with name. Will we be able do this?

I am not using where clause just use only <FieldRef Name=aaa> <FieldRef Name=bbb> and so on