How can i update a cross site list (all items) from another list with the same ID numbers

Question

I am trying to copy a list from one site to multiple other sites, i have tried to do this in powershell on an hourly basis using scheduled tasks, i was intending to write a powershell script to do the copy and set it up as a scheduled task to run each hour. The problem i have is that although i have done the above i am getting a problem which is that instead of changes to rows being updated in the copied lists, new rows are being added.

my master table looks something like this

Customer Code | Customer Name | Data 1 | Data 2
ABCD               Alphabet       abc      

I can use powershell to export this list to a cmp file and then use powershell to import this list into the other sites, what im trying to achieve is, that if the master list data is amended, when the export/imports happen the changes are added to the imported lists. At present what is happening is that if my master list were to change from the above to the following

Customer Code | Customer Name | Data 1 | Data 2
ABCD              Alphabet       abc      def 

my imported lists would all look like this

Customer Code | Customer Name | Data 1 | Data 2
ABCD             Alphabet        abc  
ABCD             Alphabet        abc      def

i.e. instead of amending the row already there, a 2nd row of data is added. As my table is a customer table and each row in the table should be a unique customer, this poses an issue for me as the local lookups to the imported lists dont update with the new info.

Here is the Powershell i am using to export, import to one site and set security on the imported list as well as removing it from the quick launch (i am a beginner at powershell and have copied and amended most of the script below to match my requirements)

Add-PsSnapin Microsoft.SharePoint.Powershell –ErrorAction SilentlyContinue

#This is the source web that is hosting the lists to move
$sourceWebUrl = "http://intranet/customerdb"

#Location to store the export file
$path = "\\MY SHAREPOINT SERVER HOSTNAME\SHARE NAME\"

#comma delimited list of List Names to copy
$lists = @("Customers")

#Loop through the lists, export the list from the source, and import the list into the destination
foreach($list in $lists)
{
"Exporting " + $sourceWebUrl + "/lists/" + $list
export-spweb $sourceWebUrl -ItemUrl ("lists/" + $list) -IncludeUserSecurity -IncludeVersions All -path ($path + $list + ".cmp") -Force
"Exporting complete."

#Import Exported List into other sites

$spBIWeb = Get-SPWeb http://intranet/BI
"Importing " + $spBIWeb + "/lists/" + $list
import-spweb $spBIWeb -IncludeUserSecurity -path ($path + $list + ".cmp") -UpdateVersions Overwrite -Force
$spBIList = $spBIWeb.GetList("http://intranet/BI/Lists/Customers")
$spBIList.OnQuickLaunch = $false
$spBIList.Update()
"Importing Complete"

#Set Security Group on Imported list with Full Control

"Setting Security"
$web = Get-SPWeb http://intranet/BI
$account = $web.EnsureUser("DOMAIN NAME\Business Intelligence Team")
$role = $web.RoleDefinitions["Full Control"]
$list = $web.Lists["Customers"]
$list.BreakRoleInheritance($true)
$assignment = New-Object Microsoft.SharePoint.SPRoleAssignment($account)
$assignment.RoleDefinitionBindings.Add($role)
$list.RoleAssignments.Add($assignment)
$list.Update()
"Security Set"

I understand that I may be able to add some CAML code into the powershell to check where the rows are the same and then update the row but I have no idea how to write CAML and my powershell is copied and amended in the main and i dont know how to set up this powershell script to make the updates if the row already exists.

Can anyone help me with what changes i need to make to the script to accomplish my goal.

Answer 1

Since your goal is to overwrite copied lists with fresh data my guess is that you only want to display main list data on other sites. There is much more simpler solution for this:

Share a List View Between Sites in SharePoint 2010

Exporting/importing lists on time interval with PS looks like overkill to me. If you really want to copy/update your data I would suggest you first delete old destination list, then recreate it and populate it with new data. However you can experience difficulties on a long run with this approach and with large amounts of data. Also you would need to take care of some leftovers eg. deleted lists in recycle bin to make things smooth.

Edited:

If destination lists are used as source for lookup column then maybe better solution is to use Lookup Field with Picker 2010. It is enhanced lookup column that supports cross site picking.